topic Re: Load script previous or peek? in QlikView

Load script previous or peek?

markp201 — Fri, 02 Mar 2018 01:54:33 GMT

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?

Re: Load script previous or peek?

Digvijay_Singh — Fri, 02 Mar 2018 04:14:31 GMT

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

));

Re: Load script previous or peek?

Digvijay_Singh — Fri, 02 Mar 2018 04:15:25 GMT

Excel sample attached here.

Re: Load script previous or peek?

Qrishna — Fri, 02 Mar 2018 04:33:30 GMT

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.

Re: Load script previous or peek?

markp201 — Fri, 02 Mar 2018 14:36:59 GMT

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],

FROM

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

WHERE

NOT ISNULL(B);

ENDIF

DATA:

NOCONCATENATE

LOAD

RESIDENT

TEMP_DATA

WHERE

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

;

DROP TABLE TEMP_DATA;

Re: Load script previous or peek?

markp201 — Fri, 02 Mar 2018 14:39:09 GMT

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