converting one row in to multiple rows

dataservices job design
1

Hi,

I am trying convert one row in to many row below is the record in coming

clientid locationid planid flightid stardate enddate
123 345 567 897 4/19/2004 4/25/2004
I need to convert this like below:

clientid locationid planid flightid date

123 345 567 897 4/19/2004 0:00
123 345 567 897 4/20/2004 0:00
123 345 567 897 4/21/2004 0:00
123 345 567 897 4/22/2004 0:00
123 345 567 897 4/23/2004 0:00
123 345 567 897 4/24/2004 0:00
123 345 567 897 4/25/2004 0:00

I don’t think using the Pivot transformation works for this since my start and end varies.

Is there other ways of doing this.

Thanks in advance.
Nalini

nalinikumar (BOB member since 2008-10-10)

2

Join the table with a Date_Generation Transform that has the entire start-end-date-range ever possible, e.g. starting 1900 until 2099.

The join-condition is

where startdate >= date_gen.date and enddate <= date_gen.date

Don’t worry about the performance, DI can join millions of rows per second this way.

Werner Daehn :de: (BOB member since 2004-12-17)

3

Thank you very much, I will try that.

Nalini

nalinikumar (BOB member since 2008-10-10)

4

I’m having problems with this.

For some reason I keep losing lots of rows.

I have a data set that contains a booking with a primary key bookingID and StartDate and EndDate

My original table has 2269 rows and if I do a count of days between start and end and sum the total, I should have 9233 individual date records, but for some reason I lose lots and end up with 1758.

Any ideas?

Klight (BOB member since 2007-07-05)

5

Do you have any Filter Conditions after that join?

ganeshxp :us: (BOB member since 2008-07-17)

6

No, nothing, just the join…it’s driving me up the wall :hb:

Klight (BOB member since 2007-07-05)

7

I would say, take up one single record at source and drive a Debug Mode and see how that record gets traversed through?

ganeshxp :us: (BOB member since 2008-07-17)

8

Will do, the annoying thing is, that it’s working fine for some records, record 517 for instance has a start date of 2011-11-07 and an end date of 2011-11-11 and it’s bringing back all the dates from 7th to 11th no problems, can’t understand why it’s doing some but not others…

The same thing happens if I do comparisons on the Julian date form just in case and it happens whether I do the join in SQL Server management studio or BODS:

SELECT * FROM DIM_JOBS1, DIM_Date
WHERE JULIANDATE>=JULIANSTART AND JULIANDATE<=JULIANEND
ORDER BY BOOKINGID;

I wish I could see a pattern to what it’s rejecting…

Klight (BOB member since 2007-07-05)

9

Find out a record, you think it is not correct and apply a filter on the source and see how it moves to each step by a Debug mode.

ganeshxp :us: (BOB member since 2008-07-17)

10

One of those Doh! moments, did an outer join to see what was missing and discovered that the Date dimension table was ending at mid november 2011…

Must have slipped when I entered the end date on the Date Generation transform!!

Thanks for your help anyway…

Klight (BOB member since 2007-07-05)

11

:mrgreen:

Werner Daehn :de: (BOB member since 2004-12-17)