Load CSV datas and calculation

[#1]

Hi,

this is my first day in Qlik Sense and I could already show some datas, this product is really awesome.

Unfortunately I've a little problem: My CSV come from an e-commerce data export. on the order table, for every order, I've 2 lines for total. One with shipping and one with order total. Unfortunately order total includes the shipping, so I've to get total without shipping but would also keep this information so I can calculate amount with and without shipping cost.

How can I do ? I've tried the group by but I get an error when I try to load datas. Here is some datas to show what I've:

record_id, orders_id, "class", value

1,1,'ot_shipping",2

2,1,'ot_total',55

3,2,'ot_shipping',4

4,2,'ot_total,87

So orders_id 1 without shipping is 53. orders_id without shipping is 83

What I look for is the values of revenue without shipping and shipping costs. I've to get individual information as I've to get informations for country, region and so on.

Thank you for help.

[maxgro · Accepted solution]

tmp:

load * inline [

record_id, orders_id, "class", value

1,1,'ot_shipping',2

2,1,'ot_total',55

3,2,'ot_shipping',4

4,2,'ot_total',87

];

final:

NoConcatenate LOAD orders_id, value as shipping_value Resident tmp where class = 'ot_shipping';

left join (final)

LOAD orders_id, value as total_value Resident tmp where class = 'ot_total';

drop table tmp;

[#3]

Grazie mille Massimo, it works like a charm.