Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
i want to get the max of the position of the max date value, i should use set analysis with firstsortedvalue, can any one help please? i have joined a capture as exemple of data, i want chose the max position of the max date of type 1:
this expression doesn't work: max({<date={"$(=firstsortedvalue({<type={1}>} date,-date))"}>} position)
desired lignes are colored with yellow:
Hi
Try like below
fund:
LOAD * INLINE [
fund, date, position, type
F1, 01/01/2021, 10, 1
F1, 01/12/2021, 9, 1
F1, 01/12/2021, 5, 1
F2, 01/03/2020, 7, 1
F2, 01/03/2020, 18, 2
F2, 01/03/2020, 19, 1
];
Join
Load fund, max(date) as date, 1 as flag Resident fund where type = 1 Group by fund;
O/p:
Hi
Try like below
fund:
LOAD * INLINE [
fund, date, position, type
F1, 01/01/2021, 10, 1
F1, 01/12/2021, 9, 1
F1, 01/12/2021, 5, 1
F2, 01/03/2020, 7, 1
F2, 01/03/2020, 18, 2
F2, 01/03/2020, 19, 1
];
Join
Load fund, max(date) as date, 1 as flag Resident fund where type = 1 Group by fund;
O/p:
You actually need a simpler expression for this: 😊
firstsortedvalue({<type={1}>} position,-date)
Hi @tresesco ,
thank you for the answer, however the formula doesn't work, i have a complicated set analysis in a star schema, the picture above is just for explaning the need, also i need the max position of the max date.
Hello @MayilVahanan ,
is there any way to achieve this without touch to the dataset, we have some requirements so we should have simple selects in load script (avoiding joins),
Might be, try like below
If(date = Max(Total<fund> date), max({<type={1}>}position))