Using routine read Hash file,generate 500 records of eachrow
Moderators: chulett, rschirm, roy
Using routine read Hash file,generate 500 records of eachrow
Hello everyone,
I have a hash file which contains 5 records for the columns A,B,C,D,E
Wherein E is my date so for every record of A(A is my Key field) i need to generate 500 records i.e if my first record is :
A = 101 , B=20,c=50,D=10,E=2003-01-01
so here i need to generate 500 records for each value of A i.e my o/p would be
101 20 50 10 2003-01-01
101 20 50 10 2003-01-02
-
-
-
101 2- 5- 10 2004-01-01
-
etc....
i.e i need to increase my date for the next 500 records
Thanks in advance
Would appreciate an earlly response....
I have a hash file which contains 5 records for the columns A,B,C,D,E
Wherein E is my date so for every record of A(A is my Key field) i need to generate 500 records i.e if my first record is :
A = 101 , B=20,c=50,D=10,E=2003-01-01
so here i need to generate 500 records for each value of A i.e my o/p would be
101 20 50 10 2003-01-01
101 20 50 10 2003-01-02
-
-
-
101 2- 5- 10 2004-01-01
-
etc....
i.e i need to increase my date for the next 500 records
Thanks in advance
Would appreciate an earlly response....
Anu_1981,
Write a routine called ExpandMe(A,B,C,D,E)
Use this routine and pass on only one column with this function result. Don't specify a column separator, quotes or line separator in your output file, the output file will have ColSep as the column separator and use <CR><LF> as the line separator. If you have comma in your data either change the ColSep character or put in quoting.
Write a routine called ExpandMe(A,B,C,D,E)
Code: Select all
EQUATE ColSep TO ',' ;** comma separator
EQUATE LineSep TO CHAR(13):CHAR(10) ;** windows CRLF
Ans = ''
InternalDate = ICONV(E,'D4-YMD')
FOR Index = 1 TO 500
OutputLine = A:ColSep:B:ColSep:C:ColSep:D:ColSep:OCONV(InternalDate+Index,'D4-YMD')
Ans := LineSep:OutputLine
NEXT Index
Ans = Ans[LEN(LineSep)+1,99999] ;** strip off extraneous LineSep
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
To write to a text file, use
You need to have opened the file with OpenSeq before the main loop, and you need to close the file with CloseSeq after the main loop (to release an update lock set by OpenSeq).
The good news is that WriteSeq looks after the line termination characters for you.
Code: Select all
WriteSeq OutputLine To filevariable
Else
Call DSLogFatal("Unable to write to output file", "ExpandMe")
End
The good news is that WriteSeq looks after the line termination characters for you.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
To read each row from a hashed file, you need to have opened the hashed file, then establish a "Select List" of the hashed file's keys. You choose one at a time and read that record.
Code: Select all
OpenPath hashedfilepath To inputfilevariable
Then
ClearSelect 9 ; * initialize Select List number 9
Select inputfilevariable To 9
Loop
While ReadNext keyvalue From 9
Read HFrecord From InputFileVariable, keyvalue
Then
A = HFrecord<1>
B = HFrecord<2>
C = HFrecord<3>
D = HFrecord<4>
E = HFrecord<5>
For Index = 1 To 500
* code goes here for generating 500 lines for this row
Next Index
End
Repeat
End
Else
Call DSLogWarn("Cannot open hashed file", "ExpandMe")
End
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.
anu,
Yes, you can do it without a function call. It is ugly and unmaintainable and not necessarily faster. The base logic is as Ray and I demonstrated, but instead of a loop you would use the STR({repeatingString},500) to generate your 500 rows per row input. I think that with your experience, the samples given above, and the manuals you can put this 1-liner together if you really want to.
Yes, you can do it without a function call. It is ugly and unmaintainable and not necessarily faster. The base logic is as Ray and I demonstrated, but instead of a loop you would use the STR({repeatingString},500) to generate your 500 rows per row input. I think that with your experience, the samples given above, and the manuals you can put this 1-liner together if you really want to.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 12
- Joined: Wed Mar 02, 2005 1:59 am
Mandar,
I think this forum is a great place to have people exchange information and, as in this case, get some assistance in coding.
I feel quite strongly that this is not the place to "give" out code. The forum shouldn't replace training but supplement it.
It should be obvious that the statement
The STR function produces a string which repeats an input string n times; the {RepeatString} I gave in the example would need to be filled with the row that you wish to repeat, then you would have to modify the dates to count up as well. All in all you should either use the routine type solution or you need to understand how DataStage functions a bit better in order to avoid using functions.
I think this forum is a great place to have people exchange information and, as in this case, get some assistance in coding.
I feel quite strongly that this is not the place to "give" out code. The forum shouldn't replace training but supplement it.
It should be obvious that the statement
will not work, even a cursory look at the PDF Basic manual will show what the STR function expects as input parameters and what it does, and I have the distinct impression that you haven't done so in this case, so I will not provide you with the solution that you want.Str(RepatString,500)
The STR function produces a string which repeats an input string n times; the {RepeatString} I gave in the example would need to be filled with the row that you wish to repeat, then you would have to modify the dates to count up as well. All in all you should either use the routine type solution or you need to understand how DataStage functions a bit better in order to avoid using functions.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The requirement to increment the date for each of the five sets of 500 rows makes this really ugly to do in a Transformer stage. A Routine will be much cleaner and easier to maintain.
One extra step if you are using the Routine to write to the text file would be to position to end-of-file after opening, using the SEEK statement.
Alternately, open into a file variable declared to be in COMMON memory and only on the first input row processed.
One extra step if you are using the Routine to write to the text file would be to position to end-of-file after opening, using the SEEK statement.
Alternately, open into a file variable declared to be in COMMON memory and only on the first input row processed.
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.