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

populate a field based on values of two different fields

Wed, 02 Apr 2014 11:51:01 GMT

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

Customer	Cust_category	Order	Order_category	OUTCOME
A	non urgent	1	non urgent	non urgent
A	non urgent	2	urgent	urgent
B	urgent	3	non urgent	urgent
C	urgent	4	urgent	urgent
C	urgent	5	non urgent	urgent
D	non urgent	6	non urgent	non 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!

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

Gysbert_Wassenaar — Wed, 02 Apr 2014 11:53:27 GMT

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 ....

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

tresB — Wed, 02 Apr 2014 11:55:49 GMT

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

MK_QSL — Wed, 02 Apr 2014 11:56:33 GMT

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

alexandros17 — Wed, 02 Apr 2014 11:57:12 GMT

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

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

Wed, 02 Apr 2014 11:57:55 GMT

it seems too lean and smart to be true

will let you know how it goes