Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 ....
Try directly(in the same load) like:
Load
*,
If( Cust_category='urgent' , 'urgent ' ,If(Order_category='urgent', 'urgent' , 'non urgent')) as OUTCOME
Form <?>;
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
];
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
it seems too lean and smart to be true
will let you know how it goes