Using routine read Hash file,generate 500 records of eachrow

[anu_1981]

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....

[anu_1981]

sorry, missed out one point we will be populating this records in the sequential file so my o/p is basically a sequential file.

[ArndW]

Anu_1981,

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 

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.

[ray.wurlod]

To write to a text file, use

Code: Select all

WriteSeq OutputLine To filevariable
Else
   Call DSLogFatal("Unable to write to output file", "ExpandMe")
End

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.

[ray.wurlod]

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

[anu_1981]

Hi All, Thanks for u r replied I ll be working on this today
Well want to know whether can we do it without using a routime
I mean at job level wherin i hav 5 records from source want to generate 500 records for each row of data as specified earlier


Thanks
Anu

[ArndW]

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.

[Sainath.Srinivasan]

If there are only 5 rows, you can generate a trans stage to provide 500 rows and obtain the values.

[Mandar_Deshpande]

Hi Sainath,

Can you be more clear on the Trans stage part i mean how to go about it?

Arnd,
I tried using Str(RepatString,500) function its not working.

[ArndW]

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

Str(RepatString,500)

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.

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.

[ray.wurlod]

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.