Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Select the last rank for every date in script

Hello,

I have a problem in script a and I would ask you to help me. I need to select only max rank of MAUC for each date !

I have a data from other ERP. The Structure of table is:

ItemDate of transactionComponentsRank of transactionMAUC
M20107.4.2017 100130 €
M20108.6.2019 100133 €
M20109.11.2019100138 €
M20109.11.2019100240 €
M20116.6.2018100195 €
M20116.6.2018500110 €
M20119.7.20191001100 €
M20119.7.2019500113 €
M20119.7.20191002102 €
M20119.7.2019500218 €
M201211.7.2019100157 €
M20142.1.20191001250 €
M20146.3.20191001300 €
M20146.3.20191002302 €
M20146.3.20191003307 €
M20146.3.20191004305 €

  

So, at first I need to select 'Date of transaction'

and then if item have more transaction in one date - select only max 'Rank of transaction' and summing components together.

Final table should be:

ItemDate of transactionMAUC (sum components)
M20107.4.201730 €
M20108.6.201933 €
M20109.11.2019 (only rank 2)40 €
M20116.6.2018105 € (components 100+500)
M20119.7.2019 (only rank 2)120 €
M201211.7.201957 €
M20142.1.2019250 €
M20146.3.2019 (only rank 4)305 €

 

Could you help me, how can  I solve this problem in load script ???

Thank you

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Creator
Creator

Thank you, It works 

View solution in original post

2 Replies
Highlighted
Partner
Partner

hi 

your script should be something like this 

dataTemp:
LOAD
    Item,
    "Date of transaction",
    Components,
    "Rank of transaction",
    MAUC
FROM [lib://data/teset.xlsx]
(ooxml, embedded labels, table is Sheet1);

//////////////////////find the max rank for each item and date combination
data:
load Item,
      "Date of transaction",
      max("Rank of transaction") as "Rank of transaction"
Resident dataTemp
group by Item, "Date of transaction";
///////////////add the sum of Mauc for the max rank found in the previous step
left join (data)
load  Item,
    "Date of transaction",
      "Rank of transaction",
      sum(MAUC) as MAUC
Resident dataTemp
group by  Item,"Date of transaction","Rank of transaction";


drop Table dataTemp;
Highlighted
Creator
Creator

Thank you, It works 

View solution in original post