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;
