Page 1 of 1
Derive Multiple Rows from two columns based on Range
Posted: Mon Sep 28, 2009 10:30 am
by 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!
Re: Derive Multiple Rows from two columns based on Range
Posted: Mon Sep 28, 2009 10:43 am
by rachit82
just4geeks wrote: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?
Posted: Mon Sep 28, 2009 10:46 am
by just4geeks
Source is a file.
Thanks!
quick and easy solution.
Posted: Mon Sep 28, 2009 10:51 am
by rachit82
just4geeks wrote: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?
Posted: Mon Sep 28, 2009 10:54 am
by 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!
Posted: Mon Sep 28, 2009 3:55 pm
by 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!
Posted: Mon Sep 28, 2009 4:10 pm
by 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...
Posted: Mon Sep 28, 2009 4:37 pm
by 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!
Posted: Tue Sep 29, 2009 2:56 am
by Sainath.Srinivasan
You can perform a lookup to your Calendar / Time dimension's year column using BETWEEN clause.