Generating Many Rows From a Single Row

[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

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

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