topic Re: Scripting Load issue in QlikView

Scripting Load issue

Sat, 12 Apr 2014 14:06:10 GMT

Afternoon .. I have a script loading data from an ODBC source as below. The section in bold and italics is consistently giving me a an error that it is unable to find the field [TypeofCover Compliance]

despite it being present and I can not spot why

The section in bold is checking for False values across a number of fields and if present adds it to a another 'master field'

F_Policy:

LOAD Policynumber as [Policy_PrimaryKey],

Customerpk,

Customerfk as [Customer_PrimaryKey],

"Broker Key" as [Broker_PrimaryKey],

Policytype as [PolicyType_PrimaryKey],

Product,

Channel,

"Type Of Cover" as [TypeOfCover_PrimaryKey],

"Maximum Cover",

date("Policy Start Date",'DD/MM/YYYY') as [Policy Start Date],

date("Policy End Date",'DD/MM/YYYY') as [Policy End Date],

"Broker Compliance" as [Broker Compliance],

"Channel Compliance" as [Channel Compliance],

"Covertype Compliance" as [TypeofCover Compliance],

"Maximumcover Compliance" as [Maximum Cover Compliance],

"Policynumber Compliance" as [Policy Number Compliance],

"Policytype Compliance" as [Policy Type Compliance],

"Product Compliance" as [Product Compliance],

If ([Broker Compliance] = 'Fail','Fail', If ([Channel Compliance] = 'Fail','Fail', If ([TypeofCover Compliance] = 'Fail','Fail'))) as [Policy Compliance],

eid as [Policy_EID];

SQL SELECT Policynumber,

Customerpk,

Customerfk,

"Broker Key",

Policytype,

Product,

Channel,

"Type Of Cover",

"Maximum Cover",

"Policy Start Date",

"Policy End Date",

"Broker Compliance",

"Channel Compliance",

"Covertype Compliance",

"Maximumcover Compliance",

"Policynumber Compliance",

"Policytype Compliance",

"Product Compliance",

eid

FROM "SCHEMA".OAUSER."F_Policy(155)" where max_rows=100000;

Re: Scripting Load issue

swuehl — Sat, 12 Apr 2014 14:20:15 GMT

Right, the [TypeofCover Compliance] is not known from the input table of that LOAD statement (you alias the field "Covertype Compliance" to that new name, but the name only exists in the output table of the LOAD).

Either use the original field name "Covertype Compliance", or use a preceding LOAD:

F_Policy:

LOAD *,

If ([Broker Compliance] = 'Fail','Fail', If ([Channel Compliance] = 'Fail','Fail', If ([TypeofCover Compliance] = 'Fail','Fail'))) as [Policy Compliance];

LOAD Policynumber as [Policy_PrimaryKey],

Customerpk,

Customerfk as [Customer_PrimaryKey],

"Broker Key" as [Broker_PrimaryKey],

Policytype as [PolicyType_PrimaryKey],

Product,

Channel,

"Type Of Cover" as [TypeOfCover_PrimaryKey],

"Maximum Cover",

date("Policy Start Date",'DD/MM/YYYY') as [Policy Start Date],

date("Policy End Date",'DD/MM/YYYY') as [Policy End Date],

"Broker Compliance" as [Broker Compliance],

"Channel Compliance" as [Channel Compliance],

"Covertype Compliance" as [TypeofCover Compliance],

"Maximumcover Compliance" as [Maximum Cover Compliance],

"Policynumber Compliance" as [Policy Number Compliance],

"Policytype Compliance" as [Policy Type Compliance],

"Product Compliance" as [Product Compliance],

eid as [Policy_EID];

SQL SELECT Policynumber,

Customerpk,

Customerfk,

"Broker Key",

Policytype,

Product,

Channel,

"Type Of Cover",

"Maximum Cover",

"Policy Start Date",

"Policy End Date",

"Broker Compliance",

"Channel Compliance",

"Covertype Compliance",

"Maximumcover Compliance",

"Policynumber Compliance",

"Policytype Compliance",

"Product Compliance",

eid

FROM "SCHEMA".OAUSER."F_Policy(155)" where max_rows=100000;

Re: Scripting Load issue

its_anandrjs — Sat, 12 Apr 2014 14:31:29 GMT

Use your script like

LOAD Policynumber as [Policy_PrimaryKey],

Customerpk,

Customerfk as [Customer_PrimaryKey],

"Broker Key" as [Broker_PrimaryKey],

Policytype as [PolicyType_PrimaryKey],

Product,

Channel,

"Type Of Cover" as [TypeOfCover_PrimaryKey],

"Maximum Cover",

date("Policy Start Date",'DD/MM/YYYY') as [Policy Start Date],

date("Policy End Date",'DD/MM/YYYY') as [Policy End Date],

"Broker Compliance" as [Broker Compliance],

"Channel Compliance" as [Channel Compliance],

"Covertype Compliance" as [TypeofCover Compliance],

"Maximumcover Compliance" as [Maximum Cover Compliance],

"Policynumber Compliance" as [Policy Number Compliance],

"Policytype Compliance" as [Policy Type Compliance],

"Product Compliance" as [Product Compliance],

If ("Broker Compliance" = 'Fail','Fail', If ("Channel Compliance" = 'Fail','Fail', If ("Covertype Compliance" = 'Fail','Fail'))) as [Policy Compliance],

eid as [Policy_EID];

SQL SELECT Policynumber,

Customerpk,

Customerfk,

"Broker Key",

Policytype,

Product,

Channel,

"Type Of Cover",

"Maximum Cover",

"Policy Start Date",

"Policy End Date",

"Broker Compliance",

"Channel Compliance",

"Covertype Compliance",

"Maximumcover Compliance",

"Policynumber Compliance",

"Policytype Compliance",

"Product Compliance",

eid

FROM "SCHEMA".OAUSER."F_Policy(155)" where max_rows=100000;

other wise use preceding load as swuehl suggest

LOAD *,

If ([Broker Compliance] = 'Fail','Fail', If ([Channel Compliance] = 'Fail','Fail', If ([TypeofCover Compliance] = 'Fail','Fail'))) as [Policy Compliance];

Load

your statements

...

Re: Scripting Load issue

Sat, 12 Apr 2014 14:52:40 GMT

Thanks First one it still does not like.

I will try the preceding one

Robin

Re: Scripting Load issue

its_anandrjs — Sat, 12 Apr 2014 14:58:37 GMT

then use a script like

F_Policy:

LOAD *,

If ([Broker Compliance] = 'Fail','Fail', If ([Channel Compliance] = 'Fail','Fail', If ([TypeofCover Compliance] = 'Fail','Fail'))) as [Policy Compliance];

LOAD Policynumber as [Policy_PrimaryKey],

Customerpk,

Customerfk as [Customer_PrimaryKey],

"Broker Key" as [Broker_PrimaryKey],

Policytype as [PolicyType_PrimaryKey],

Product,

Channel,

"Type Of Cover" as [TypeOfCover_PrimaryKey],

"Maximum Cover",

date("Policy Start Date",'DD/MM/YYYY') as [Policy Start Date],

date("Policy End Date",'DD/MM/YYYY') as [Policy End Date],

"Broker Compliance" as [Broker Compliance],

"Channel Compliance" as [Channel Compliance],

"Covertype Compliance" as [TypeofCover Compliance],

"Maximumcover Compliance" as [Maximum Cover Compliance],

"Policynumber Compliance" as [Policy Number Compliance],

"Policytype Compliance" as [Policy Type Compliance],

"Product Compliance" as [Product Compliance],

eid as [Policy_EID];

SQL SELECT Policynumber,

Customerpk,

Customerfk,

"Broker Key",

Policytype,

Product,

Channel,

"Type Of Cover",

"Maximum Cover",

"Policy Start Date",

"Policy End Date",

"Broker Compliance",

"Channel Compliance",

"Covertype Compliance",

"Maximumcover Compliance",

"Policynumber Compliance",

"Policytype Compliance",

"Product Compliance",

eid

FROM "SCHEMA".OAUSER."F_Policy(155)" where max_rows=100000;

Yes swuehl is right.

Re: Scripting Load issue

PrashantSangle — Sat, 12 Apr 2014 14:59:33 GMT

Hi,

It should work as swuehl suggest or

you can try with resident load like,

load your above script without if statement then

write

LOAD *,

If ([Broker Compliance] = 'Fail','Fail', If ([Channel Compliance] = 'Fail','Fail', If ([TypeofCover Compliance] = 'Fail','Fail'))) as [Policy Compliance]

Resident F_Policy;

so that you can use newly created column in your resident table.

Regards,

Re: Scripting Load issue

arsal_90 — Sat, 12 Apr 2014 15:22:33 GMT

Make the qvd of that table and then apply that if condition on that it's a good practice to call your original table on extract layer and then do transformation on another layer

Re: Scripting Load issue

Sat, 12 Apr 2014 16:06:41 GMT

Thanks one and all .. I used the Resident and a few temp tables and it works as expected.

Cheers and have a good weekend

Robin