Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
markp201
Creator III
Creator III

Load script previous or peek?

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

Report file

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

Output

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

Would we previous, peek or something else?

1 Solution

Accepted Solutions
markp201
Creator III
Creator III
Author

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;

View solution in original post

5 Replies
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)))

));

Capture.PNG

Digvijay_Singh

Excel sample attached here.

krishna_2644
Specialist III
Specialist III

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.

1.PNG

markp201
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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