Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Load CSV datas and calculation

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Load CSV datas and calculation

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;

2 Replies
MVP
MVP

Re: Load CSV datas and calculation

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;

Not applicable

Re: Load CSV datas and calculation

Grazie mille Massimo, it works like a charm.