topic Execution os script failed. reload old data? but with no indication of whats' wrong in QlikView

Execution os script failed. reload old data? but with no indication of whats' wrong

Tue, 08 Sep 2009 21:32:50 GMT

I am extracting data from 4 tables, 3 of the tables are liked correctly and with one partially linked the extract runs fine.

If I add the other link required which means changing the script to us the 'AS' function to just rename the required field I get the above error, but no indication in the "Script Execution Progess" box.

How do I switch on DEBUG mode, or how is the best way to identiry what's going wrong.

Execution os script failed. reload old data? but with no indication of whats' wrong

amit_shetty78 — Tue, 08 Sep 2009 21:38:18 GMT

Hi,

You can enable the option to Generate Log file in the document properties to see the error.

For debug mode, you need to click on Debug in the Edit Script option.

Hope this helps.

Execution os script failed. reload old data? but with no indication of whats' wrong

Tue, 08 Sep 2009 21:45:12 GMT

When you are in the "edit script area" next to "reload" you'll see "debug" click on debug, then select animate. This will go through each load statement in your script to help you identify where it is failing. For additional details you can see the log file as well.

The debug will prevent it from closing the script on you as well.

Execution os script failed. reload old data? but with no indication of whats' wrong

Tue, 08 Sep 2009 21:51:00 GMT

OK tried both of those but nothing stands out to me, Log attached.

08/09/2009 15:45:21: Execution started.
08/09/2009 15:45:21: QlikView Version:9.00.7119.4
08/09/2009 15:45:21: 0002 SET ThousandSep=','
08/09/2009 15:45:21: 0003 SET DecimalSep='.'
08/09/2009 15:45:21: 0004 SET MoneyThousandSep=','
08/09/2009 15:45:21: 0005 SET MoneyDecimalSep='.'
08/09/2009 15:45:21: 0006 SET MoneyFormat='£#,##0.00;-£#,##0.00'
08/09/2009 15:45:21: 0007 SET TimeFormat='hh:mm:ss'
08/09/2009 15:45:21: 0008 SET DateFormat='DD/MM/YYYY'
08/09/2009 15:45:21: 0009 SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]'
08/09/2009 15:45:21: 0010 SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'
08/09/2009 15:45:21: 0011 SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'
08/09/2009 15:45:21: 0013 Load
08/09/2009 15:45:21: 0014
08/09/2009 15:45:21: 0015 fin_date,
08/09/2009 15:45:21: 0016 fin_period,
08/09/2009 15:45:21: 0017 fin_qtr,
08/09/2009 15:45:21: 0018 fin_week,
08/09/2009 15:45:21: 0019 period_week,
08/09/2009 15:45:21: 0020 fin_year,
08/09/2009 15:45:21: 0021 year_week,
08/09/2009 15:45:21: 0022 cal_year,
08/09/2009 15:45:21: 0023 fin_year_full,
08/09/2009 15:45:21: 0024 tax_week as period,
08/09/2009 15:45:21: 0025 tax_year as Year
08/09/2009 15:45:21: 0028 ODBC CONNECT*
08/09/2009 15:45:21: 0029 SQL SELECT *
08/09/2009 15:45:21: 0030 FROM cdlive.dbo."fin_cal"
08/09/2009 15:45:21: 0031 where fin_year_full > 2005
08/09/2009 15:45:21: 11 fields found: fin_date, fin_period, fin_qtr, fin_week, period_week, fin_year, year_week, cal_year, fin_year_full, period, Year, 1,461 lines fetched
08/09/2009 15:45:21: 0033 load
08/09/2009 15:45:21: 0034
08/09/2009 15:45:21: 0035 works_order, operation_code, posting_date as fin_date, customer_code, batch_name, batch_posting_date,
08/09/2009 15:45:21: 0036 resource_type, resource_used, resource_name,
08/09/2009 15:45:21: 0037 setter_type, setter_used, setter_name, operator_type, operator_used as employee_number,
08/09/2009 15:45:21: 0038 operator_name, kind, completed_flag, milestone_op, from_sequence_no,
08/09/2009 15:45:21: 0039 setup_time_booked, op_time_booked, deltay_time_booked, quantity_received,
08/09/2009 15:45:21: 0040 quantity_passed_on, quantity_rejected, operation_cost, labour_cost,
08/09/2009 15:45:21: 0041 overhead_cost, subcontract_cost, reject_cost, Labour_reject_cost, ohead_reject_cost,
08/09/2009 15:45:21: 0042 subcon_reject_cost, tran_comment, decimal_places, negative
08/09/2009 15:45:21: 0044 SQL SELECT *
08/09/2009 15:45:21: 0045 FROM pelive.dbo."wip_hpc"
08/09/2009 15:45:26: 36 fields found: works_order, operation_code, fin_date, customer_code, batch_name, batch_posting_date, resource_type, resource_used, resource_name, setter_type, setter_used, setter_name, operator_type, employee_number, operator_name, kind, completed_flag, milestone_op, from_sequence_no, setup_time_booked, op_time_booked, deltay_time_booked, quantity_received, quantity_passed_on, quantity_rejected, operation_cost, labour_cost, overhead_cost, subcontract_cost, reject_cost, Labour_reject_cost, ohead_reject_cost, subcon_reject_cost, tran_comment, decimal_places, negative, 279,957 lines fetched
08/09/2009 15:45:53: 0047 load
08/09/2009 15:45:53: 0048 employee_number, period, item, spare, val, Year
08/09/2009 15:45:53: 0050 SQL SELECT *
08/09/2009 15:45:53: 0051 FROM HPC.dbo."pyhistm_hpc"
08/09/2009 15:45:53: 0052 where payroll = 'W1'
08/09/2009 15:45:53: 0053 and item like 'HRS%'
08/09/2009 15:45:53: 6 fields found: employee_number, period, item, spare, val, Year, 47,556 lines fetched
08/09/2009 15:45:54: 0055 Load department,description
08/09/2009 15:45:54: 0056 SQL SELECT *
08/09/2009 15:45:54: 0057 FROM cdlive.scheme.pydeptm
08/09/2009 15:45:54: 0058 where description like 'PE %' or description like 'HYD%'
08/09/2009 15:45:54: 2 fields found: department, description, 16 lines fetched
08/09/2009 15:45:54: 0059 left join (pydeptm)
08/09/2009 15:45:54: 0060
08/09/2009 15:45:54: 0061
08/09/2009 15:45:54: 0062 load
08/09/2009 15:45:54: 0063 employee_number, department,job_family,surname & ' '& forename_1 as Employee
08/09/2009 15:45:54: 0065 SQL SELECT *
08/09/2009 15:45:54: 0066 FROM cdlive.scheme.pypersonm
08/09/2009 15:45:54: 0067 where payroll = 'W1'
08/09/2009 15:45:54: 4 fields found: employee_number, department, job_family, Employee, 200 lines fetched
08/09/2009 15:45:54: Joining/Keeping
08/09/2009 15:45:54: $Syn 1 = period+Year
08/09/2009 15:45:54: General Script Error
08/09/2009 15:45:54: Execution Failed
08/09/2009 15:45:54: Execution finished.

Execution os script failed. reload old data? but with no indication of whats' wrong

Tue, 08 Sep 2009 21:53:59 GMT

Sorry should have said it the following line that is causing the problem

08/09/2009 15:45:21: 0037 setter_type, setter_used, setter_name, operator_type, operator_used as employee_number,

if I remove "as employee_number" the script runs.

The filed I am linking to contains the matching data ans the field types are both char10's

Execution os script failed. reload old data? but with no indication of whats' wrong

amit_shetty78 — Tue, 08 Sep 2009 21:59:05 GMT

I guess you need to enclose the alias in square brackets since the alias has a special character.

Try this: as [employee_number]

Execution os script failed. reload old data? but with no indication of whats' wrong

Tue, 08 Sep 2009 22:08:17 GMT

Didnt work unfortunatley, I have also tried demoting the tab but no joy with that either.

Execution os script failed. reload old data? but with no indication of whats' wrong

Anonymous — Tue, 08 Sep 2009 22:18:06 GMT

The first step - get rid of the synthetic key. There are fields "period"and "Year" in two tables.

Execution os script failed. reload old data? but with no indication of whats' wrong

Tue, 08 Sep 2009 22:59:46 GMT

I have removed the synthetic date by using Year & period as year_period and only bringing thorugh year and period into one table which will always contain that data.

However this hasnt helped with the main issue.

Execution os script failed. reload old data? but with no indication of whats' wrong

Anonymous — Tue, 08 Sep 2009 23:22:47 GMT

OK, thanks. The next step - could you please reload without this "as employee_number", and post the data model (CTRL+T) of the result?
Two more questions:
1. What QV version?
2. Can you try to reload in another version?

Execution os script failed. reload old data? but with no indication of whats' wrong

Tue, 08 Sep 2009 23:27:15 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/7331.datamodel.doc:550:0]

If I remove the "as employee_number" the script runs without error

I am using version 9.00.7119.4, I can download an earlier version if you want me to try.

Execution os script failed. reload old data? but with no indication of whats' wrong

Anonymous — Tue, 08 Sep 2009 23:45:22 GMT

Clear enough, thanks.
You have empoyee_number field in two tables already - pydept and pyhistm_hpc. When you add a field with the same name to table wip_hpc, you create logical loop. You cannot use this field name here in your current logical design.
(No need to try another QV. I know that in QV 8.01 and earlier you'll be able to reload it, but QV will make a table or two "loosely coupled". QV 9, and probably 8.50 simply fail.)

Execution os script failed. reload old data? but with no indication of whats' wrong

Wed, 09 Sep 2009 02:08:11 GMT

So I guess I need to remove the pydeptm table and any info I need from that extract with the pyhistm_hpc data as I only need description from pydeptm

Execution os script failed. reload old data? but with no indication of whats' wrong

Anonymous — Wed, 09 Sep 2009 02:28:12 GMT

I think removing pydempt is not a solution. You'll get:
employee_number in pyhistm_hpc and wip_hpc
year_period in pyhistm_hpc and and fin_cal
fin_date in fin_cal and wip_hpc
So, one of these must have different field name

Execution os script failed. reload old data? but with no indication of whats' wrong

Wed, 09 Sep 2009 02:35:14 GMT

Michael not quite sure what you are saying now, problem is I have the employee name is in three tables which is needed along with other fields to make unique not sure what to do now.

Steve

Execution os script failed. reload old data? but with no indication of whats' wrong

Anonymous — Wed, 09 Sep 2009 04:49:41 GMT

Steve,
Here is what you have, if renaming to "employee_number".
1. Table pyhistm_hpc linked to table wip_hpc by employee_number.
2. Table pyhistm_hpc linked to table fin_cal by year_period.
3. Table wip_hpc linked to table fin_cal by fin_date.
You can see that it is a logical loop - circular reference. QV 9 reload fails for exactly this reason.
To be able to reload, you have to break the loop in any place. If you must have empoyee_number in both pyhistm_hpc and wip_hpc, you have to break this loop by renaming either year_period or fin_date in any table.
Another approach is to change your data model, maybe by joining two of the three tables in that loop. Maybe even all three. But this is defined mostly by your business needs, so I'm not in a good position to tell you what approach is the best in this case. Try and see.

Execution os script failed. reload old data? but with no indication of whats' wrong

Wed, 09 Sep 2009 20:31:59 GMT

Michael

Got it sorted I think, I have attached my data model, but basically taken the data from pypersonm and pydeptm and loaded it with pyhistm and loaded fin_cal in with wip_hpc.

Just need to sort out the synthetic links now.

thanks Steve