How do I dynamically generate rows?

[gillu]

Hi,

I want to build a job that generates rows for each row that coming from the source.
The problem is that the number of rows is specific for each row and determined by calculation.
Example:

• Source:
  Start_date, End_date, dummy
  12/10/2005, 12/10/2005, aaa
  12/10/2005, 14/10/2005, bbb
  
  Target:
  12/10/2005, 12/10/2005, aaa
  12/10/2005, 14/10/2005, bbb
  13/10/2005, 14/10/2005, bbb
  14/10/2005, 14/10/2005, bbb

What I did in the example is that I generated a row for each day in the range between start_date and end_date.
The first row in the source is producing only one row and the second row produce 3 rows because there are 3 days between these dates.

Appreciate the help,
Gil

[I_Server_Whale]

Welcome, Aboard.

You can achieve this by using Stage variables and custom routines.

Since you need to generate new rows only

If 'the difference between End_Date & Start_Date' > 0 Else 'Do not generate'

First, calculate the difference between the dates. Then write a custom routine which checks every incoming row if the difference of dates is greater than one day to add rows.

I have done this before. Try it.

Thanks,
Naveen.

[gillu]

Thanks Naveen.
But, I still don't understand how do I duplicate the rows with the routine.
I already knew how to do this calculation with the dates and how to put the constraint...

Thanks,
Gil

[DSguru2B]

Hey gillu,
As naveen said you will need to write a routine for it. I was just playing with your data in my free time. the best that i could come with is as follows.

-create a stage var say myVal
-call a custom routine in there pass all your three columns in there.
basically myroutine(trim(Start_date),trim(End_date),trim(dummy))
- in your output, have three columns defined as you have in your target
-in the derivation of start_date, pass the myVal (the stage variable)
-in the other two columns, pass @null

your routine will have

Code: Select all

St_Dt = Arg1
En_Dt = Arg2
Dmy = Arg3
x = 0
z=''
Day1 = St_Dt[1,2]
Day2 = En_Dt[1,2]


y = Day1
For x = Day1 To Day2

y = y:St_Dt[3,8]
z = y
y = y[1,2]+1

Ans = Ans:St_Dt:",":z:",":Dmy:char(010)
Next x


Return(Ans)

- in your target seq. file, specify the quote character as 000 and delimiter as ,(comma)

that will do the trick.
the only problem is, that after every row that needs to be generated multiple times, it creates a blank row. if you can handle that somehow, your all set.
try it and let me know
cheers

[rleishman]

An alternate idea (I've never tried it, so it may not work):
Say you had a Hashed file containing the calendar with no missing dates. Take your source file and write it to either another Hashed File or a UV stage (UniVerse table).

Then you should be able to use the UV stage to perform a SQL join on the Calendar file/table with your hashed input file

Code: Select all

WHERE cal.dt BETWEEN inp.start AND inp.end

I'm probably over-simplifying it, but its a possibility...