Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone!
Im having an issue with synthetic keys causing my script load to take FOREVER and no data actually appears.
I have a code that pulls multiple excel tabs fom multiple excel files. The files I use ar enot large at all, but for some reason Qlikview is giving me a hard time. Can anyone give a suggestion within my code?
For Each file in FileList('LOCATION OF FOLDER HERE\*.xlsx');
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
tables:
SQLtables;
DISCONNECT;
FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load
*
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);
NEXT i
Drop Tables Table;
Next file
What is SQLtables;?
Can you share if you have some code here?
SQLtables will give fields from the tables within the ODBC connection
Try to use Qualify on fields for which you don't want associations..
107 synthetic keys? probably qlikview is loading the same table multiple times.If you need all the tables,then you have rename the fields by using various methods like Qualify,Using 'As', etc.if you need all the data in one table try concatenating/merging all the data into one table.
not sure what your scenario is.
please share your excel file with some sample data in it.
Thanks
If you do not really understand what we are suggesting, then you can try the following.
QlikView will still create syntehtic keys, but not as many. And they won't block your access to the loaded document.
Then press Ctrl-T and check your data model. Is this what you expect it to be? If not, check Krishna's post because it contains various solutions for this problem. Or ask away.
Best,
Peter
I think this is the right solution.
We tend to store the tables and drop the tables at the end of each iteration.
Synth. Keys are not bad, but they do tend to make the loading process take longer.
Kind Regards,
Dion
I have several files. Each files contains 4 tabs.
In each tab will be various amounts of data that will all have the same column names, but the information within will vary. I've attached an image with an idea of the data that will be used.I would just like to load all of the data from the multiple tabs from multiple excel files.
I would like to have the data distinct and without concatenation.
If the script you posted is accurate, then I can see at least one problem.
Drop Tables Table;
I think you meant
Drop Tables Tables;
If you drop Table instead of Tables, then you will keep adding to the Tables list and load every file many times.
You should concatenate these loads.
Before your file loop begins, prepare a Table to concat to:
Table:
LOAD 0 as dummyField autogenerate 0;
In your Load of the xls file:
Concatenate (Table) LOAD ...
At the end of all your loops:
DROP FIELD dummyField;
-Rob