topic Re: Synthetic Keys slows down script execution in QlikView

Synthetic Keys slows down script execution

Anonymous — Wed, 14 Mar 2018 18:55:31 GMT

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

Re: Synthetic Keys slows down script execution

Digvijay_Singh — Wed, 14 Mar 2018 19:01:26 GMT

What is SQLtables;?

Can you share if you have some code here?

Re: Synthetic Keys slows down script execution

Anonymous — Wed, 14 Mar 2018 19:10:06 GMT

SQLtables will give fields from the tables within the ODBC connection

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

Re: Synthetic Keys slows down script execution

MK_QSL — Wed, 14 Mar 2018 19:17:40 GMT

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

Re: Synthetic Keys slows down script execution

Qrishna — Wed, 14 Mar 2018 19:23:40 GMT

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

Re: Synthetic Keys slows down script execution

Peter_Cammaert — Wed, 14 Mar 2018 19:47:59 GMT

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

Re: Synthetic Keys slows down script execution

dionverbeke — Wed, 14 Mar 2018 19:51:33 GMT

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

Re: Synthetic Keys slows down script execution

Anonymous — Fri, 16 Mar 2018 14:31:43 GMT

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.

Re: Synthetic Keys slows down script execution

Anonymous — Fri, 16 Mar 2018 14:32:38 GMT

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

Re: Synthetic Keys slows down script execution

rwunderlich — Fri, 16 Mar 2018 17:17:22 GMT

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

Re: Synthetic Keys slows down script execution

Qrishna — Sat, 17 Mar 2018 07:36:53 GMT

If you want all the tables with no concatenation, uSe 'Qualify' key before each table load.this ensure you have all the tables without formation of any synthetic keys.

Please post atleast some sample files.

Re: Synthetic Keys slows down script execution

Anonymous — Sat, 17 Mar 2018 12:46:02 GMT

I agree with Peter, it's the best solution!

qualify / unqualify

drop table

drop field