topic Re: Select the last rank for every date in script in App Development

Select the last rank for every date in script

jozisvk11 — Sat, 16 Nov 2024 02:34:09 GMT

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:

Item	Date of transaction	Components	Rank of transaction	MAUC
M2010	7.4.2017	100	1	30 €
M2010	8.6.2019	100	1	33 €
M2010	9.11.2019	100	1	38 €
M2010	9.11.2019	100	2	40 €
M2011	6.6.2018	100	1	95 €
M2011	6.6.2018	500	1	10 €
M2011	9.7.2019	100	1	100 €
M2011	9.7.2019	500	1	13 €
M2011	9.7.2019	100	2	102 €
M2011	9.7.2019	500	2	18 €
M2012	11.7.2019	100	1	57 €
M2014	2.1.2019	100	1	250 €
M2014	6.3.2019	100	1	300 €
M2014	6.3.2019	100	2	302 €
M2014	6.3.2019	100	3	307 €
M2014	6.3.2019	100	4	305 €

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:

Item	Date of transaction	MAUC (sum components)
M2010	7.4.2017	30 €
M2010	8.6.2019	33 €
M2010	9.11.2019 (only rank 2)	40 €
M2011	6.6.2018	105 € (components 100+500)
M2011	9.7.2019 (only rank 2)	120 €
M2012	11.7.2019	57 €
M2014	2.1.2019	250 €
M2014	6.3.2019 (only rank 4)	305 €

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

Thank you

Re: Select the last rank for every date in script

lironbaram — Tue, 28 Apr 2020 11:43:57 GMT

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;

Re: Select the last rank for every date in script

jozisvk11 — Tue, 28 Apr 2020 12:09:06 GMT

Thank you, It works