Qliksenes conditional Load statement

[YanMin]

Hi guys,

I have an difficulty on adding conditional filed when loading data. In my data, transactions fees are shown by order ID. Each order got different products. So if there are more than 1 product in each order, the calculation for Transaction fees if wrong because it is for each order and not for each item. So my logic is to get individual transaction fees for each product. So I try load with condition as followed.

Data_Test:
Load
"Order ID",
if(count("Order ID")> 1, num("Transaction Fee(Incl. GST)"/count("Transaction Fee(Incl. GST)")),"Transaction Fee(Incl. GST)")
Resident Shopee_OrderID_Transfee group by "Order ID";

But I got an invalid expression error. Can anyone advise how should the script be? 

Thanks in advanced.

[marcus_sommer · Accepted solution]

Each field which isn't included within an aggrgation-function must be added to the group by. This means in your case you couldn't solve your challenge within a single load else the counting needs to be done on the outside. Maybe with something like this:

m: mapping load orderid, count(orderid) from source group by orderid;

t: load *, fee / applymap('m', orderid, 1) as AverageFeePerItem from source;

Another approach would be to check with interrecord-functions like peek() and/or previous() if it's the first/last record per orderid to flag it or to remove the fee-information from the other records.

It depends on your later views which approach is more suitable.

- Marcus

[YanMin]

Hi Marcus,

The first method is perfectly work. Now I understand about aggregation function. Thank you so much.