Derive Multiple Rows from two columns based on Range

[just4geeks]

I have to derive Multiple Rows from two columns based on Range :

For Example:
Source File looks like:
Col1 Col 2 Col3
A 2004 2006
B 1999 2002

From this I have to a derive a column (Col4) which should have all values within the range of Col2 and col3

So My Output file should look like:

Col1 Col4
A 2004
A 2005
A 2006
B 1999
B 2000
B 2001
B 2002

I will appreciate if anybody can help me designing this logic efficiently in DataStage 7.0 server version.

Thanks in advance,

Take care!

[rachit82]

I have to derive Multiple Rows from two columns based on Range :

For Example:
Source File looks like:
Col1 Col 2 Col3
A 2004 2006
B 1999 2002

From this I have a derive a column (Col4) which should have all values within the range of Col2 and col3

So My Output file should look like:

Col1 Col4
A 2004
A 2005
A 2006
B 1999
B 2000
B 2001
B 2002

I will appreciate if anybody can help me designing this logic efficiently in DataStage 7.0 server version.

Thanks in advance,

Take care!

Is this a file you get or is it a table that is your source?

[just4geeks]

Source is a file.

Thanks!

[rachit82]

Source is a file.

Thanks!

Quick and dirty way would be to put it on a staging table and then do a between statement where you can pass the year number as a parameter which would yeild the desired result.

If you do not want to use staging tables then tell me more about this range. Is it always a date or what values do these columns have?

[just4geeks]

Thanks for the response!

The 2 columns ( Col2 and col3 ) will always have year values. For example 1999, 2001, 2002, 2003 etc.

Take care!

[just4geeks]

Any suggestions are most welcome for this interesting problem.
source is a flat file and I can't use staging table. I have to write DataStage logic to accomplish this.
Take care!

[chulett]

What is your target?

Build a routine with a FOR loop, appending the values from your range. If your target is another sequential file, you can even add CHAR(10) characters between them to create individual records from the returned string. Crude illustration:

Code: Select all

YearString=""
FOR year = Col2 to Col3 step 1
  YearString := Col1:",":year:CHAR(10)
NEXT year
Ans=YearString

Hopefully that will get the creative juices flowing...

[just4geeks]

Thanks Craig!
My Traget is also a sequential file.
I am not well versed in writing routines but I will try this one tomorrow.
Take care!

[Sainath.Srinivasan]

You can perform a lookup to your Calendar / Time dimension's year column using BETWEEN clause.