Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am quite noob with the Qlik Sense so I have to ask some support from here.
I have package delivery data from external logistics system where all the delivered and returned packages are recorded. I would like to calculate what are the actual logistic costs of the orders.
The issue that I am facing is, that with Customer ID there can be duplicate rows because the customer returns are recorded with the similar process in the system. Really simple example what my table looks like + cost variables as an example:
As an result I would like to have something following:
I tried the pivot table, it was able to group the delivery types to the Customer ID's but I could not the costs and Return Yes/No to be working.
Thanks in advance if someone is able to guide me! 🙂
You'd have to transform your data a little.
First, get the types of deliveries excluding "Return", giving express and dispenser options.
Next, left join with the available customer id that have "Return" delivery type, to get the "Return" field.
Lastly, you could get by the delivery type, the corresponding Delivery cost.
The complete code would be something like this:
let vExpress = 5;
let vDispenser = 4;
let vReturn = 4;
Data:
load * Inline
[
Customer ID,Delivery type
7372223,Express post
7372230,Package dispenser
7372230,Return
7372249,Express post
7372257,Express post
7372257,Return
];
Mapping
Map_Types:
Load * inline
[
Type,Num
Express post,1
Package dispenser,2
];
NoConcatenate
exclude_return:
load
*,
ApplyMap('Map_Types',[Delivery type]) as Mapped_delivery
Resident
Data
where
[Delivery type] <> 'Return';
left join (exclude_return)
load
[Customer ID],
'Yes' as [Return]
resident
Data
where
[Delivery type] = 'Return';
final_table:
load
*,
[Delivery Cost]+[Return Cost] as [Total Cost];
load
[Customer ID],
[Delivery type],
if(Mapped_delivery=1,$(vExpress),$(vDispenser)) as [Delivery Cost],
if(isNull(Return),'No',Return) as Return,
if(isNull(Return),0,$(vReturn)) as [Return Cost]
resident exclude_return;
drop tables exclude_return,Data;
This code gives me the following:
Hope it helps.
Hi Arttu,
Hear is the sample script
Trans:
load * Inline [
CUSTID,DELIVERYTYPE
7372223,'Express Post'
7372230,'Package Dispenser'
7372230,'Return'
7372249,'Express Post'
7372257,'Express Post'
7372257,'Return'
];
Price:
load * Inline [
DELIVERYTYPE,PRICE
'Express Post',5
'Package Dispenser',4
'Return',4
];
Delivery Cost :
SUM({<DELIVERYTYPE=-{'Return'}>}AGGR(Count({<DELIVERYTYPE=-{'Return'}>}CUSTID)*SUM({<DELIVERYTYPE=-{'Return'}>}PRICE),CUSTID))
Return:
IF(sum({<DELIVERYTYPE-={'Return'}>}aggr(Count({<DELIVERYTYPE={'Return'}>}CUSTID),CUSTID))=0,'No','Yes')
Return Cost :
Sum({<DELIVERYTYPE-={'Return'}>}aggr(Count({<DELIVERYTYPE={'Return'}>}CUSTID)*SUM({<DELIVERYTYPE={'Return'}>}PRICE),CUSTID))
Total Cost :
SUM({<DELIVERYTYPE=-{'Return'}>}AGGR((Count({<DELIVERYTYPE=-{'Return'}>}CUSTID)*SUM({<DELIVERYTYPE=-{'Return'}>}PRICE))+(Count({<DELIVERYTYPE={'Return'}>}CUSTID)*SUM({<DELIVERYTYPE={'Return'}>}PRICE)),CUSTID))
Hope it helps...
Another solution:
The four measures - last four columns:
Hello,
Thanks for the support! I will start looking into these! 🙂
[ NOTE: to show any contributor that you like and appreciate their answers please CLICK the like icon (thumbs up) ]