Scripting Load issue

[#1]

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;

[swuehl]

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;

[its_anandrjs]

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

..

...

[#4]

Thanks First one it still does not like.

I will try the preceding one

Robin

[its_anandrjs]

Ok

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.

[PrashantSangle · Accepted solution]

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,

[arsal_90]

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

[#8]

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