Load script previous or peek?

[markp201]

We need to read a formatted report with groups.  We need the group header on each row of the detail

Report file

Header 1	Header 2	Header 3
Group 1
	Value 1	Value 2
	Value 3	Value 4
Group 2
	Value 5	Value 6
	Value 7	Value 8

Output

Header 1	Header 2	Header 3
Group 1	Value 1	Value 2
Group 1	Value 3	Value 4
Group 2	Value 5	Value 6
Group 2	Value 7	Value 8

Would we previous, peek or something else?

[Digvijay_Singh]

Try this script, I generated it through transformations steps while loading excel file.

Table1:

LOAD [Header 1],

     [Header 2],

     [Header 3]

FROM

[..\..\..\..\..\..\Downloads\293355.xlsx]

(ooxml, embedded labels, table is Sheet1, filters(

Replace(1, top, StrCnd(null)),

Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

));

[Digvijay_Singh]

Excel sample attached here.

[krishna_2644]

check this out in case you are not using any external file and loading the data using resident loads.

you can use both peek and previous, in the qvw attached i've used peek function though.

[markp201 · Accepted solution]

Peek and Previous are quite powerful.  Below is my solution

FOR EACH vFile IN FILELIST('<somefolder>\*.xlsx')

LET vMonth = MID(vFile,28,2);

LET vDate=MID(vFile,24,8);

TEMP_DATA:

LOAD

  $(vDate)                     AS [Report Date],

  rowno()                    AS [Report Row],

  recno()                    AS [Report Record],

  IF(A='Service Type',PREVIOUS(A),PEEK('Section Header')) AS [Section Header],

  A,

  B,

  C,

  D,

  E,

  F,

  G,

  H

FROM

  $(vFile) (ooxml, no labels, table is Sheet1)

WHERE

  NOT ISNULL(B);

ENDIF

NEXT

DATA:

NOCONCATENATE

LOAD

  *

RESIDENT

  TEMP_DATA

WHERE

  NOT (A='Service Type' OR ISNULL(A)) !

;

DROP TABLE TEMP_DATA;

[markp201]

Sorry about that - I didn't get a chance to read through your script before resolving this on my own.