Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Arttu
Contributor II
Contributor II

Analyzing duplicate rows

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:

Delivery.PNG

As an result I would like to have something following:

Result.PNG

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! 🙂

5 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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:

sample_img.png

 

Hope it helps.

kaanerisen
Creator III
Creator III

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))

 

 

Untitled.png

Hope it helps...

petter
Partner - Champion III
Partner - Champion III

Another solution:

LOAD SCRIPTLOAD SCRIPT(STRAIGHT) TABLE(STRAIGHT) TABLE

The four measures - last four columns:

CostsCosts
ReturnReturn
Total CostsTotal Costs
Merknad 2019-07-18 160635.png

Arttu
Contributor II
Contributor II
Author

Hello,

Thanks for the support! I will start looking into these! 🙂

petter
Partner - Champion III
Partner - Champion III

[ NOTE: to show any contributor that you like and appreciate their answers please CLICK the like icon (thumbs up) ]