Page 1 of 1

How do I dynamically generate rows?

Posted: Tue Mar 21, 2006 10:21 am
by 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

Posted: Tue Mar 21, 2006 10:52 am
by 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.

Posted: Wed Mar 22, 2006 9:25 am
by 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

Posted: Wed Mar 22, 2006 12:17 pm
by 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

Posted: Thu Mar 23, 2006 1:17 am
by 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...