topic Re: Conditional Load in QlikView

Conditional Load

hvfalcao — Fri, 09 Jan 2015 22:34:50 GMT

Hello!

How to load data based on other table field value?

I have this tables load with sql select.

tab CITY:

idcity,

namecity;

tab ORDER;

idorder,

idcustomer,

idcustomerprop,
qtyorder;

tab CUSTOMER:

idcustomer,

idcity;

tab CUSTOMER_PROP:
idcustomerprop,

idcustomer,

idcity

the condition I need on load:

IF ORDER.idcustomerprop is null THEN CUSTOMER.idcity = CUSTOMER_PROP.idcity else CUSTOMER.idcity = CUSTOMER.idcity

Each CUSTOMER, have one MAIN CITY and might have others PROPRIETIES, with different CITY.

On table ORDER, if the field idcustomerprop have any value, this means that the ORDER is related to the CUSTOMER_PROP city, and if the same field is NULL then, the ORDER is related to the main CUSTOMER city.

When doing this on a SQL QUERY, I use case when, but I don't know how to solve this on load script...

Sorry if I couldn't explain better.

Thanks

Re: Conditional Load

Peter_Cammaert — Sat, 10 Jan 2015 00:04:57 GMT

How about:

MapCustPropCity:

MAPPING LOAD idcustomer & '|' & idcustomerprop, idcity;

SQL SELECT * FROM CUSTOMER_PROP;

MapCustCity:

MAPPING LOAD idcustomer, idcity;

SQL SELECT * FROM CUSTOMER;

LOAD idorder, idcustomer, idcustomerprop, qtyorder,

if (IsNull(idcustomerprop), applymap('MapCustCity', idcustomer),

applymap('MapCustPropCity', idcustomer & '|' & idcustomerprop)) AS idcity;

SELECT * FROM ORDER;

Adjust the different SQL statements so that they fit your situation.

You can also accomplish the same with a few LEFT JOINS and a RESIDENT LOAD, but I think the Mapping Table-approach is faster.

Peter

Re: Conditional Load

hvfalcao — Sat, 10 Jan 2015 13:15:47 GMT

Peter,

Just perfect... Thank you very much!

Henrique