Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Synthetic Keys slows down script execution

    Hi everyone!

Im having an issue with synthetic keys causing my script load to take FOREVER and no data actually appears.

Script Execution.PNG

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

11 Replies
Digvijay_Singh

What is SQLtables;?

Can you share if you have some code here?

Anonymous
Not applicable
Author

SQLtables will give fields from the tables within the ODBC connection

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptRegularS...

MK_QSL
MVP
MVP

Try to use Qualify on fields for which you don't want associations..

krishna_2644
Specialist III
Specialist III

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

If you do not really understand what we are suggesting, then you can try the following.

  • The synthetic keys are eventually blocking the creation of your data model. There are simply too many.
  • Synthetic keys should be avoided as much as possible
  • The technique to do that is probably very simple, but if QlikView won't grant you access to the document after it loaded all those tables, then you're stuck.
  • A way to escape from this situation and observe what is going wrong with your script is to create a new temporary folder and copy three of the excel source files into that folder to test.
  • Then change the pink path in your  FOR EACH statement so that it points to the temporary folder and reload.

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

dionverbeke
Luminary Alumni
Luminary Alumni

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

Anonymous
Not applicable
Author

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.

Sample Data.PNG

Anonymous
Not applicable
Author

I would like to have the data distinct and without concatenation.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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