Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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)))
));
Excel sample attached here.
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.
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;
Sorry about that - I didn't get a chance to read through your script before resolving this on my own.