Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thank you, It works
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;
Thank you, It works