Load script stuck

[bmenicucci]

Hello community,

I'm experiencing a quite strange behaviour in one of my applications and it seems I'm not able to get going, so I'm going to ask your help for this:

The script is very simple (please see hereafter), it connects to a mySql server and tries to show all tables present.

The strange thing is that if I let the script go, it keeps running and filling the memory: clock is spinning and everything is stuck.

On the contrary, if I light on debugger, and limit the number of the records to be load, everything goes fine.

Please note that in mySql the "Show Tables" command get back 115 records.

Any idea?

Thanks a lot for your support!

SCRIPT:

ODBC CONNECT TO mydatabase;

Tables:

LOAD Tables_in_mydatabase as Tabella;

SQL show TABLES;

//Loop

FOR i=1 to FieldValueCount('Tabella');

LET vFileName = FieldValue('Tabella',$(i));

LOAD

  *

  ,'$(vFileName)' AS Tabella

;

SQL SELECT * FROM mydatabase.$(vFileName);

NEXT i;

[Colin-Albert]

You are loading all fields from every SQL table into QlikView, this is going to generate a massive amount of synthetic keys and will be very inefficient  - if the script actually finishes!

Try loading data from one table first, load and view the results in QlikView. Then add a second table, again load and view the results before you try a third table. This will give you some understanding of how QlikView loads tables. Replicating a SQL schema in QlikView does not create a good data model.

Have a look here for more information on QlikView modelling http://community.qlik.com/docs/DOC-7343

[Peter_Cammaert]

Two ideas:

• Maybe your 115 table just contain too much data?
• An RDBMS often has tables with fields with identical field names. An RDBMS won't create associations but QlikView will. Result: lots of large synthetic keys. Use the document log to see if the script runs till the end, and only then gets stuck. Add QUALIFY; UNQUALIFY statements around the last LOAD/SELECT combo to do away with them.

BTW Add this line between the LET and LOAD statements in your FOR loop:

:

[$(vFileName)]:

:

[bmenicucci]

Hello Colin,

thanks a lot for your prompt reply....

Actually, the script gets stuck while executing the FIRST sql command that should give back only 115 records. Instead, if I limit the records, say to 1000, everything seems ok.

I've also tried the following script, that I found googling around, but the result it's the same: the script stucks while executing SQL command. Never experienced this!

SQLTableList:

LOAD TABLE_NAME as  TableNames;

SQL SELECT DISTINCT TABLE_NAME

FROM information_schema.COLUMNS

WHERE TABLE_NAME like 'cr_%';

Let vTableCount = NoOfRows('SQLTableList');

Let vTableCountQVD = 'dbo.[' & NoOfRows('SQLTableList') & ']';

For i = 0 To $(vTableCount) -1

LET vMyTableName = 'dbo.[' & Peek('TableNames', $(i), 'SQLTableList') & ']';

LET vMyTableNameQVD = Replace(Peek('TableNames', $(i), 'SQLTableList'), ' ', '');

$(vMyTableNameQVD):

SQL SELECT *FROM $(vMyTableName);

STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;

DROP Table $(vMyTableNameQVD);

Next i

[Colin-Albert]

Can you add some trace commands to the script to view if the variables are correct.

The square brackets will ley you see any leading or trailing spaces.

trace vMyTableName = [$(vMyTableName)] ;

The other option is to run the script in debug mode with a Limited Load of 10 rows and step through the script or use Animate

[bmenicucci]

Hi You all,

sorry for my late reply, I've been quite busy here around.

Actually I'm not able to retrieve data from the database.

I've been focusing on this simple statement and it doesn't work!

ODBC CONNECT TO centralerischi (XUserId is XXXXXX, XPassword is XXXXXXX);

[My Table]:

LOAD *;

SQL SELECT *

FROM centralerischi.`cr_12_record`;

Qlikview seems to go into a loop... please take a look to the following link http://cl.ly/3j1s0W2X0031/download/test1.qvw.2015_01_19_15_00_00.log

I've tried to create a new app from scratch but didn't succeed. Also, as you can see, I followed your suggestion to trying load a single table... without any chance.!

But the most strange thing is that if I set a limited load in debugging, say 1000 records, everything is fine

[bmenicucci · Accepted solution]

I've solved this using a "FIRST (xxx) LOAD" statement.

With a 1 million "first load" (I'm sure I'll never reach it) everything is going fine.

Thank to you all for the support.

Brunello

[jagan]

Hi,

Never load all the tables, rows and all columns.  Many of the tables may not be used in all the dashboards and all rows and columns are not going to use sometimes, so manually write the script to load specific tables and columns.  This is a good practice, otherwise you will face this type of issues.

Hope this helps you.

Regards,

Jagan.