Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm trying to load a .xlsx file with multiple worksheets with the same structure, however, as I do the load of records tables are not created for each worksheet, only the first, and made some kind of aggregation of lines of all spreadsheets, I will illustrate to become
Load Script
January:
LOAD [Partner / Fantasy]
[Company Name]
[URL / Website]
Area,
FROM
(OOXML, embedded labels, header is 5 lines, table is [January 2015]);
February:
LOAD [Partner / Fantasy]
[Company Name]
[URL / Website]
Area,
FROM
(OOXML, embedded labels, header is 5 lines, table is [February 2015]);
When I debug it returns
<< Janeiro in January 2015 176 records read
<< January February 2015
--- --- Finished Script
Can anyone point out what am I doing wrong? When we withdraw fields of the queries work.
It looks like since all spreadsheets have the same structure, QlikView automatically concatenates the data in a single table when you create the qvd. To avoid that, you could add the statement NOCONCATENATE at the start of every spreadsheet capture, for example:
Load Script
January:
LOAD [Partner / Fantasy]
[Company Name]
[URL / Website]
Area,
FROM
(OOXML, embedded labels, header is 5 lines, table is [January 2015]);
NOCONCATENATE
February:
LOAD [Partner / Fantasy]
[Company Name]
[URL / Website]
Area,
FROM
(OOXML, embedded labels, header is 5 lines, table is [February 2015]);
HOWEVER... that leads to more problems because you will end up with a lot synthetic keys.
Tables of the same structure will be automatically concatenated.
Since you are loading almost the same data from different tables, having different tables might not be the best solution. Instead, consider loading them into the same table but add a flag for each sheet.
-------------------------------
AllDataInOneTable:
LOAD [Partner / Fantasy]
[Company Name],
[URL / Website],
Area,
"January 2015" as MonthYear
FROM
(OOXML, embedded labels, header is 5 lines, table is [January 2015]);
LOAD [Partner / Fantasy]
[Company Name]
[URL / Website]
Area,
"February 2015" as MonthYear
FROM
(OOXML, embedded labels, header is 5 lines, table is [February 2015]);