Page 1 of 1
Generating Many Rows From a Single Row
Posted: Fri Apr 28, 2006 8:27 am
by kld05
Can someone please point me down the correct path. I have a requirement to generate many rows based on the columns values of a single row and writing the row only if the date is a weekday (holidays are not a concern)
For example that the following record:
300,20060420,20060425
I need to generate a row for all business days from 20060420 to 20060425 (4 records output)
300,20060420,20060420
300,20060420,20060421
300,20060420,20060424
300,20060420,20060425
Posted: Fri Apr 28, 2006 3:31 pm
by ray.wurlod
Welcome aboard. :D
This is probably most easily accomplished with a server routine invoked via a Routine activity in a job sequence.
Code: Select all
FUNCTION GenerateRows(SourceFile, TargetFile)
$INCLUDE UNIVERSE.INCLUDE FILEINFO.H
DEFFUN OpenTextFile(FileName, OpenMode, WriteMode, Logging) Calling "DSU.OpenSequentialFile"
Ans = -1
* Open source file for reading, target file for writing (overwrite).
hSource = OpenTextFile(SourceFile, "R", "A", "Y")
hTarget = OpenTextFile(TargetFile, "W", "O", "Y")
If FileInfo(hSource, FINFO$IS.FILEVAR) And FileInfo(hTarget, FINFO$IS.FILEVAR) Then
Ans = 0 ; * return value
WriteCount = 0 ; * number of lines written
Spec = "D-YMD[4,2,2]" : @VM : "MCN" ; * Oconv specification
* Process each row in the source file.
Loop
While ReadSeq Line From hSource
* Decompose into components. No checking for valid dates here.
* (Though there should be.)
ID = Field(Line, ",", 1, 1)
StartDate = Iconv(Field(Line, ",", 2, 1), "DYMD")
EndDate= Iconv(Field(Line, ",", 3, 1), "DYMD")
* Generate lines and write to output.
For Date = StartDate To EndDate
Line = ID : "," : Oconv(StartDate, Spec) : "," : OConv(Date, Spec)
WriteSeq Line To hTarget Then WriteCount += 1
Next Date
Repeat
CloseSeq hTarget
CloseSeq hSource
End
RETURN(Ans)
Search the Forum to find the source for OpenSequentialFile routine.
Posted: Mon May 01, 2006 1:59 pm
by bcarlson
Another possibility is a buildop. Below is a posting dealing with multiple row output:
Splitting One Record to Many
You'll need to add your date logic, but this example shows how to generate the multi-row output.
Brad.