Hi,
I have a source row which has a format as follow:
Cu_id---Cu_nm---Start_date---End_date----Revenue%
10----ABC-----09/01/2005---10/11/2005--- 40
I need the Target to be as follows:
Cu_id---Cu_nm---Date----------Revenue%
10-----ABC------09/01/2005---- 40
10-----ABC------09/02/2005---- 40
10-----ABC------09/03/2005---- 40
10-----ABC------09/04/2005---- 40
and so on till 10/11/2005.
Taking the difference of days between the start _date and End_date. I have to replicate same data those number of days, i.e, for each day.
For the above example: 1 Source row should be having 31 Target rows.
Any suggestion to implement the above logic would be helpful.
Thanks
Logic Implementation
Moderators: chulett, rschirm, roy
Logic Implementation
Every person you meet knows something you don't, Learn from them.
-- H. Jackson Brown
-- H. Jackson Brown
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Probably the easiest way to do this is to write a routine to generate a long string which produces all the output rows with LF between. Write this to a sequential file (as a single VarChar column) with 000 as the delimiter and quote character and then read from the same sequential file using whatever delimiters your routine has added in.
Code: Select all
FUNCTION GenerateRows(Col1, Col2, Col3, Col4, Col5)
Equate DELIM To ","
Equate LF To Char(10)
StartDate = Iconv(Col3, "DMDY")
EndDate = Iconv(Col4, "DMDY")
Ans = ""
For TheDate = StartDate To EndDate
Ans<-1> = Col1 : DELIM : Col2 : DELIM : Oconv(TheDate, "D/MDY[2,2,4]") : DELIM : Col4
Next TheDate
Convert @FM To LF In Ans
RETURN(Ans)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
ray.wurlod wrote:Probably the easiest way to do this is to write a routine to generate a long string which produces all the output rows with LF between. Write this to a sequential file (as a single VarChar column) with 000 as the delimiter and quote character and then read from the same sequential file using whatever delimiters your routine has added in.Code: Select all
FUNCTION GenerateRows(Col1, Col2, Col3, Col4, Col5) Equate DELIM To "," Equate LF To Char(10) StartDate = Iconv(Col3, "DMDY") EndDate = Iconv(Col4, "DMDY") Ans = "" For TheDate = StartDate To EndDate Ans<-1> = Col1 : DELIM : Col2 : DELIM : Oconv(TheDate, "D/MDY[2,2,4]") : DELIM : Col4 Next TheDate Convert @FM To LF In Ans RETURN(Ans)
Hi Ray,
Thanks for the input. Actually i am not much familiar writing routines, Do the routine needs to be a transform function Or Before/After Subroutine.Can you please explain me little more in detail.
Thanks in advance.
Every person you meet knows something you don't, Learn from them.
-- H. Jackson Brown
-- H. Jackson Brown
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
The one mentioned in post is transformer routine with 5 args.
Only transformer routines can take more than one args directly.
You just have to create a new routine using DS Manager or Designer. Procedure to create routine is available in the PDFs or help for DS. Its very easy.
Only transformer routines can take more than one args directly.
You just have to create a new routine using DS Manager or Designer. Procedure to create routine is available in the PDFs or help for DS. Its very easy.
Regards,
S. Kirtikumar.
S. Kirtikumar.
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
The one mentioned in post is transformer routine with 5 args.
Only transformer routines can take more than one args directly.
You just have to create a new routine using DS Manager or Designer. Procedure to create routine is available in the PDFs or help for DS. Its very easy.
Only transformer routines can take more than one args directly.
You just have to create a new routine using DS Manager or Designer. Procedure to create routine is available in the PDFs or help for DS. Its very easy.
Regards,
S. Kirtikumar.
S. Kirtikumar.
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
The one mentioned in post is transformer routine with 5 args.
Only transformer routines can take more than one args directly.
You just have to create a new routine using DS Manager or Designer. Procedure to create routine is available in the PDFs or help for DS. Its very easy.
Only transformer routines can take more than one args directly.
You just have to create a new routine using DS Manager or Designer. Procedure to create routine is available in the PDFs or help for DS. Its very easy.
Regards,
S. Kirtikumar.
S. Kirtikumar.
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
The one mentioned in post is transformer routine with 5 args.
Only transformer routines can take more than one args directly.
You just have to create a new routine using DS Manager or Designer. Procedure to create routine is available in the PDFs or help for DS. Its very easy.
Only transformer routines can take more than one args directly.
You just have to create a new routine using DS Manager or Designer. Procedure to create routine is available in the PDFs or help for DS. Its very easy.
Regards,
S. Kirtikumar.
S. Kirtikumar.
Thanks Kirti. I will try.Kirtikumar wrote:The one mentioned in post is transformer routine with 5 args.
Only transformer routines can take more than one args directly.
You just have to create a new routine using DS Manager or Designer. Procedure to create routine is available in the PDFs or help for DS. Its very easy.
Every person you meet knows something you don't, Learn from them.
-- H. Jackson Brown
-- H. Jackson Brown