Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

populate a field based on values of two different fields

Dear all,

I have the following, for which I cannot decide the best method how to tackle this

Basically, the first four columns is the scenario and the fifth one, outcome, is the outcome I plan to get

CustomerCust_categoryOrderOrder_categoryOUTCOME
Anon urgent1non urgentnon urgent
Anon urgent2urgenturgent
Burgent3

non urgent

urgent
Curgent4urgenturgent
Curgent5non urgenturgent
Dnon urgent6non urgentnon urgent

Shall I achieve this by doing two resident loads?

first, loading the 4 columns and generating the Outcome column?

and then reloading this data, and if (outcome=non urgent) but customer=urgent, assign the urgent flag?

Or, is there a better way?

thank you!

5 Replies

Re: populate a field based on values of two different fields

If you load the first four columns from one source table you can use a simple if statement to create the OUTCOME column. If the four columns come from different tables then you first need to join those tables into one table.

load

     Customer,

     Cust_category,

     Order,

     Order_category,

     if(Cust_category='urgent' or Order_category = 'urgent', 'urgent','not urgent') as OUTCOME

from ....


talk is cheap, supply exceeds demand
MVP
MVP

Re: populate a field based on values of two different fields

Try directly(in the same load) like:

Load     

          *,

          If( Cust_category='urgent' , 'urgent ' ,If(Order_category='urgent', 'urgent' , 'non urgent')) as OUTCOME

Form <?>;

Re: populate a field based on values of two different fields

Load

  *,

  IF(Cust_category = 'urgent' or  Order_category = 'urgent', 'urgent','non urgent') as Outcome

Inline

[

Customer, Cust_category, Order, Order_category

A, non urgent, 1, non urgent

A, non urgent, 2, urgent

B, urgent, 3, non urgent

C, urgent, 4, urgent

C, urgent, 5, non urgent

D, non urgent, 6, non urgent

];

Re: populate a field based on values of two different fields

I think yours is the best way:

Load *,

     if(cust_category='urgent' or order_category='urgent', 'urgent', 'non urgent') as OUTCOME

Load * resident myTable;

Hope it helps

Not applicable

Re: populate a field based on values of two different fields

it seems too lean and smart to be true

will let you know how it goes

Community Browser