topic Re: Script execution reaches end, but doesn't conclude, and no error messages in QlikView

Script execution reaches end, but doesn't conclude, and no error messages

stevelord — Wed, 21 May 2014 18:09:41 GMT

Hi, I've pressed reload on a dashboard, and it spent a few minutes running through the script. The Script Execution Progress window shows it has reached what I know is the last table and appears to have completely loaded it, but the dialogue window is not closing itself even though I have that checkbox selected. It is also not letting me close it with the end here button, and I've had to close qlikview with task manager a couple times now.

Can someone provide me possible scenarios and solutions for this phenomenon? The dashboard is on qlikview server and is live, I was just sneaking in a couple more fields and reloading it. There are no errors in the script that I can see, and the Task Manager shows CPU Usage at ~25% and Physical Memory hanging at 80%. (I've pushed the first past 80% and the second to 99% without crashing anything, so pretty sure those aren't the issue.)

Re: Script execution reaches end, but doesn't conclude, and no error messages

arthur_dom — Wed, 21 May 2014 18:16:49 GMT

Maybe there is some problem with Read/write file. Copy the App and try again. If have store commands check the files of store, they could be the problem.

Re: Script execution reaches end, but doesn't conclude, and no error messages

hic — Wed, 21 May 2014 18:22:04 GMT

This is the stage in the script where QlikView establishes the data model and creates the synthetic keys. If you have many synthetic keys, the script will hang at this stage.

I would suggest you load just a small amount of data - either by using "First 10" before each load statement, or by making a "Limited Load" in the script debugger - so you will be able to analyze the data model.

HIC

Re: Script execution reaches end, but doesn't conclude, and no error messages

stevelord — Wed, 21 May 2014 20:45:09 GMT

I had added a resident load in script to do some more formulas for additional fields, and neglected to drop the previous table, so a massive Synthetic key ensued. Adding the drop table after the latest resident load cleared the synthetic key and let the dashboard refresh. Thanks!

PS> I have had issues in the past where someone or something was accessing a file the same time the dashboard was trying to access it, causing the dashboard to error, so gave the other response a helpful answer mark for that. (I did run around and make sure that wasn't the issue before I saw this correct answer response here.)

Re: Script execution reaches end, but doesn't conclude, and no error messages

Karim_Khan — Mon, 21 Dec 2015 10:43:48 GMT

Hi HIC,

Even after applying 10 limited rows debugging its not working again facing the same issue.

Regards,

Karim Khan

Re: Script execution reaches end, but doesn't conclude, and no error messages

stevelord — Mon, 21 Dec 2015 13:45:24 GMT

Hi Karim, In the document, open settings-> Document Properties. And on the General tab, check the box for Generate Logfile in the upper right, then check Timestamp in Logfile Name.

Then go into your qvw and try the debug again. Maybe right before you go to lunch and check on it during/after lunch. (Safest if you can run it on your local desktop.)

The logfile is written as the script runs, so at worst you should see where it's hung even if the reload doesn't finish. Then you can comment out that piece of script and the piece after it. If the script runs okay after that, then you know the issue is something in that piece of script you commented out.

Other thing to do is be sure you're QV is update to 11.2 SR 12 - Doing that fixed a few random other issues I had with other things in the past too. Lastly if it's just not reloading from QV Management Console, you might try rebooting the server to give the QV services and console a clean start.

Edit: Server reboot is a last resort during business hours, but easy to get away with outside business hours (unless it's a 24/7 customer-facing dashboard, then you might stick with 5am EST or something appropriate for your business.)

A couple more thoughts: In all likelihood the issue will be something with synthetic keys - if you have very many tables even 10 records might be a lot. If you have many tables with a mix of the same fields, you might think about letting only one table bring in certain fields and all tables have one common key. If your key needs to be multiple fields, you might concatenate those fields into a single key field like: field1&field2 as KeyField then relabel or comment out the component fields to keep them unique between the tables.

Or if nearly all the fields are the same, but not quite all, you might just concatenate load the second table onto the first or left join onto a key field etc. (Do a bit of trial and error to be sure result is a correct data model.) Generally best to spell everything out and not let Qlikview try to figure out the intention.

Re: Script execution reaches end, but doesn't conclude, and no error messages

Karim_Khan — Mon, 21 Dec 2015 13:59:54 GMT

HI Steve,

Thanks for your reply I will work on above mention things.I am little confused about last part because I have developer version only.I don't know how and where to clear the QV services and console clearance.

Once again thanks will check your solution hope it will work

Regards,

Karim Khan

Re: Script execution reaches end, but doesn't conclude, and no error messages

stevelord — Mon, 21 Dec 2015 14:08:23 GMT

Yeah, by all means skip the qlikview server/management console suggestions if you don't have those. Going to help-qlikview update is a good place to start, then the more arduous troubleshooting after that.

Re: Script execution reaches end, but doesn't conclude, and no error messages

Karim_Khan — Mon, 21 Dec 2015 14:12:01 GMT

Its a single table only

Re: Script execution reaches end, but doesn't conclude, and no error messages

stevelord — Mon, 21 Dec 2015 14:24:02 GMT

Are you able to share the script? Not sure how one table could get stuck. Maybe if the database table you're querying is locked by someone else's query (they forget to write (nolock) or with nolock into the end of their query). Or an interesting syntax error in the script itself though I can't think of what that would be.

Re: Script execution reaches end, but doesn't conclude, and no error messages

Karim_Khan — Mon, 21 Dec 2015 15:01:18 GMT

Hi Steve,

PFB Error logs Data and script

Logs:

12/21/2015 8:26:48 PM: Execution started.

12/21/2015 8:26:48 PM: QlikView Version:11.00.11154.0

12/21/2015 8:26:48 PM: CPU Target x64

12/21/2015 8:26:48 PM: Operating System Windows 7 Professional Service Pack 1 (64 bit edition)

12/21/2015 8:26:48 PM: Wow64 mode Not using Wow64

12/21/2015 8:26:48 PM: MDAC Version 6.1.7601.17514

12/21/2015 8:26:48 PM: MDAC Full Install Version 6.1.7601.17514

12/21/2015 8:26:48 PM: PreferredCompression 2

12/21/2015 8:26:48 PM: EnableParallelReload 1

12/21/2015 8:26:48 PM: ParallelizeQvdLoads 1

12/21/2015 8:26:48 PM: AutoSaveAfterReload 0

12/21/2015 8:26:48 PM: BackupBeforeReload 1

12/21/2015 8:26:48 PM: EnableFlushLog 0

12/21/2015 8:26:48 PM: SaveInfoWhenSavingFile 0

12/21/2015 8:26:48 PM: UserLogfileCharset 0

12/21/2015 8:26:48 PM: OdbcLoginTimeout -1

12/21/2015 8:26:48 PM: OdbcConnectionTimeout -1

12/21/2015 8:26:48 PM: ScriptWantsDbWrite false

12/21/2015 8:26:48 PM: ScriptWantsExe false

12/21/2015 8:26:48 PM: LogFile CodePage Used: 1252

12/21/2015 8:26:56 PM: 0002 SET ThousandSep=','

12/21/2015 8:26:56 PM: 0003 SET DecimalSep='.'

12/21/2015 8:26:56 PM: 0004 SET MoneyThousandSep=','

12/21/2015 8:26:56 PM: 0005 SET MoneyDecimalSep='.'

12/21/2015 8:26:56 PM: 0006 SET MoneyFormat='$#,##0.00;($#,##0.00)'

12/21/2015 8:26:56 PM: 0007 SET TimeFormat='h:mm:ss TT'

12/21/2015 8:26:56 PM: 0008 SET DateFormat='M/D/YYYY'

12/21/2015 8:26:56 PM: 0009 SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'

12/21/2015 8:26:56 PM: 0010 SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

12/21/2015 8:26:56 PM: 0011 SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'

12/21/2015 8:26:56 PM: 0017 CUSTOM CONNECT*Provider*JDBCConnector_x64.dll*XUserId*XPassword*

12/21/2015 8:27:01 PM: 0018 Directory

12/21/2015 8:27:01 PM: 0019 T_WAR_PRODUCT:

12/21/2015 8:27:01 PM: 0020

12/21/2015 8:27:01 PM: 0021

12/21/2015 8:27:01 PM: 0022

12/21/2015 8:27:01 PM: 0023 SQL SELECT *

12/21/2015 8:27:01 PM: 0024 FROM ucpprd.ucp."t_war_product"

12/21/2015 8:27:03 PM: 16 fields found: visit_dt, visitor_id, visit_nbr, ip_addr, hit_seq_nbr, hit_ts, site_nm, site_section_nm, product_nm, page_nm, product_view_cnt, product_edu_view_cnt, trial_initiate_cnt, cloud_initiate_cnt, edu_download_start_cnt, dt, 100 lines fetched

Script code:

CUSTOM CONNECT TO "Provider=JDBCConnector_x64.dll;jdbc:redshift://ucpprd.cusjr55gdzdb.us-east-1.redshift.amazonaws.com:5439/ucpprd?ssl=true&sslfactory=com.amazon.redshift.ssl.NonValidatingFactory;XUserId=CTCZMadLULaOXQVNNF;XPassword=RBfVGadLULaOXQVNDJQIWZNJRRMGDUC;";

Directory;

T_WAR_PRODUCT:

//First 1000000

//LOAD *;

SQL SELECT *

FROM ucpprd.ucp."t_war_product";

STORE T_WAR_PRODUCT INTO D:\UCP\KK_WAR_PRODUCT.qvd (qvd);

Re: Script execution reaches end, but doesn't conclude, and no error messages

Karim_Khan — Mon, 21 Dec 2015 15:19:50 GMT

Hi Steve,

I have noticed the error if i am fetching 10 columns then in the result i am getting 10 columns data and extra comma is coming .don't know why its coming and how to resolve it?

Error_Logs:

12/21/2015 8:46:14 PM: 0023 SQL SELECT visit_dt, visitor_id, visit_nbr, ip_addr, hit_seq_nbr, hit_ts, site_nm, site_section_nm, product_nm, page_nm, product_view_cnt, product_edu_view_cnt, trial_initiate_cnt, cloud_initiate_cnt, edu_download_start_cnt, dt

12/21/2015 8:46:14 PM: 0024 FROM ucp.t_war_product

12/21/2015 8:46:16 PM: 16 fields found: visit_dt, visitor_id, visit_nbr, ip_addr, hit_seq_nbr, hit_ts, site_nm, site_section_nm, product_nm, page_nm, product_view_cnt, product_edu_view_cnt, trial_initiate_cnt, cloud_initiate_cnt, edu_download_start_cnt, dt, 10 lines fetched

Regards,

Re: Script execution reaches end, but doesn't conclude, and no error messages

stevelord — Mon, 21 Dec 2015 15:52:08 GMT

Hi Karim, I think you need to remove the // from in front of your LOAD *; or you're telling Qlikview to select things but not load anything. Like so:

//First 1000000

LOAD *;

SQL SELECT *

FROM ucpprd.ucp."t_war_product";

After that, the //First 1000000 can be left out probably (with the // in front of it it's not doing anything now though). I see it only fetched 100 rows from the select statement, so not sure what would happen if you tried to tell it to load the first million and only a hundred were present.

Lastly, I have noticed in my own data model that if I try to load 'the first 10' and those rows happen to be null values in the fields I selected, it will load nothing rather than skip to the rows that have values. (Commonly happens if the first bunch of records are just test records and test user didn't populate all fields. First 1000 or first 10000 tends to get me past those test people in our data set.)

Edit: You will also want to edit your post to truncate or scramble the xuserid and xpassword stuff. The xpassword is encrypted by Qlikview, but people with advanced knowledge of know how that works might know how to decrypt it. (I always just go with abc123 as a placeholder in forum posts and just woe to anyone who actually uses abc123 for a password. )

Re: Script execution reaches end, but doesn't conclude, and no error messages

Karim_Khan — Mon, 21 Dec 2015 15:57:38 GMT

Then why it is not working

FIRST 10

SQL SELECT *

from ucpprd.ucp."t_war_product";

I have noticed the error if am fetching 16 columns from my table then in the result its giving me the 16 columns plus extra comma(,)is coming due to which it is going into infinite loop now am investigating that only how to sort this.

Regards,

Re: Script execution reaches end, but doesn't conclude, and no error messages

stevelord — Mon, 21 Dec 2015 17:02:51 GMT

Hi Karim, instead of SQL Select *; you could list the fields and see if that solves the issue. Infinite loop sounds like a good culprit for something never finishing though. I do believe you need to keep a Load *; above your SQL Select if you want qlikview to actually hold any of the data for you to use on the front end too.

Load *;

SQL Select

field1,

field2,

field3

FROM table;

Re: Script execution reaches end, but doesn't conclude, and no error messages

Karim_Khan — Mon, 21 Dec 2015 17:18:41 GMT

Hi Steve,

I have tried your suggestion also now some new log data is genrated but script execution is not getting finished.

12/21/2015 10:45:06 PM: Execution started.

12/21/2015 10:45:06 PM: QlikView Version:11.00.11154.0

12/21/2015 10:45:06 PM: CPU Target x64

12/21/2015 10:45:06 PM: Operating System Windows 7 Professional Service Pack 1 (64 bit edition)

12/21/2015 10:45:06 PM: Wow64 mode Not using Wow64

12/21/2015 10:45:06 PM: MDAC Version 6.1.7601.17514

12/21/2015 10:45:06 PM: MDAC Full Install Version 6.1.7601.17514

12/21/2015 10:45:06 PM: PreferredCompression 2

12/21/2015 10:45:06 PM: EnableParallelReload 1

12/21/2015 10:45:06 PM: ParallelizeQvdLoads 1

12/21/2015 10:45:06 PM: AutoSaveAfterReload 0

12/21/2015 10:45:06 PM: BackupBeforeReload 1

12/21/2015 10:45:06 PM: EnableFlushLog 0

12/21/2015 10:45:06 PM: SaveInfoWhenSavingFile 0

12/21/2015 10:45:06 PM: UserLogfileCharset 0

12/21/2015 10:45:06 PM: OdbcLoginTimeout -1

12/21/2015 10:45:06 PM: OdbcConnectionTimeout -1

12/21/2015 10:45:06 PM: ScriptWantsDbWrite false

12/21/2015 10:45:06 PM: ScriptWantsExe false

12/21/2015 10:45:06 PM: LogFile CodePage Used: 1252

12/21/2015 10:45:06 PM: 0002 SET ThousandSep=','

12/21/2015 10:45:06 PM: 0003 SET DecimalSep='.'

12/21/2015 10:45:06 PM: 0004 SET MoneyThousandSep=','

12/21/2015 10:45:06 PM: 0005 SET MoneyDecimalSep='.'

12/21/2015 10:45:06 PM: 0006 SET MoneyFormat='$#,##0.00;($#,##0.00)'

12/21/2015 10:45:06 PM: 0007 SET TimeFormat='h:mm:ss TT'

12/21/2015 10:45:06 PM: 0008 SET DateFormat='M/D/YYYY'

12/21/2015 10:45:06 PM: 0009 SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'

12/21/2015 10:45:06 PM: 0010 SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

12/21/2015 10:45:06 PM: 0011 SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'

12/21/2015 10:45:06 PM: 0012 SET ErrorMode=0

12/21/2015 10:45:06 PM: 0014 CUSTOM CONNECT*Provider*JDBCConnector_x64.dll*XUserId*XPassword*

12/21/2015 10:45:11 PM: 0016 FIRST 10

12/21/2015 10:45:11 PM: 0017 LOAD *

12/21/2015 10:45:11 PM: 0018 SQL SELECT visit_dt, visitor_id, visit_nbr, ip_addr, hit_seq_nbr, hit_ts, site_nm, site_section_nm, product_nm, page_nm, product_view_cnt, product_edu_view_cnt, trial_initiate_cnt, cloud_initiate_cnt, edu_download_start_cnt, dt

12/21/2015 10:45:11 PM: 0019 from ucpprd.ucp."t_war_product"

12/21/2015 10:45:12 PM: 16 fields found: visit_dt, visitor_id, visit_nbr, ip_addr, hit_seq_nbr, hit_ts, site_nm, site_section_nm, product_nm, page_nm, product_view_cnt, product_edu_view_cnt, trial_initiate_cnt, cloud_initiate_cnt, edu_download_start_cnt, dt, 10 lines fetched

12/21/2015 10:45:13 PM: 0023 STORE KK_PRODUCT INTO D:\UCP\KK_UAT.QVD(QVD)

Regards,

Re: Script execution reaches end, but doesn't conclude, and no error messages

Karim_Khan — Mon, 21 Dec 2015 17:22:41 GMT