Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
RESOLVED : Thanks a lot @maxgro
Hello all, i was wondering something,
If we have data like this
FirstName | LastName | Product bought | Date of payment |
Paul | Sirk | World of Warcaft | 02/07/2019 |
Paul | Sirk | Guild wars 2 | 09/12/2020 |
Florent | Polo | Flyff | 01/02/2017 |
Florent | Polo | World of Warcraft | 08/09/2019 |
If I want to group by these data and make group with FirstName and Lastname and I want to find the product bought at the most recent date of payment, how would i do ?
( i mean a result tab like this : )
Paul | Sirk | Guild wars 2 | 09/12/2020 |
Florent | Polo | World of Warcraft | 08/09/2019 |
I was thinking about
Firstname,
Lastname,
max(Date of payment)
Group by Firstname, Lastname ?
But to have the product bought of the max date of payment how to do ? Is it forced to do a sort by before ?
Thanks a lot for reading !
try with FirstSortedValue in the script
table1:
load * inline [
FirstName LastName "Product bought" "Date of payment"
Paul Sirk "World of Warcaft" 02/07/2019
Paul Sirk "Guild wars 2" 09/12/2020
Florent Polo Flyff 01/02/2017
Florent Polo "World of Warcraft" 08/09/2019
] (delimiter is spaces)
;
table2:
LOAD
FirstName,
LastName,
FirstSortedValue("Product bought", -"Date of payment") as Product,
Date(Max("Date of payment")) as "Date of payment"
RESIDENT
table1
GROUP BY
FirstName,
LastName
;
DROP TABLE table1;
try with FirstSortedValue in the script
table1:
load * inline [
FirstName LastName "Product bought" "Date of payment"
Paul Sirk "World of Warcaft" 02/07/2019
Paul Sirk "Guild wars 2" 09/12/2020
Florent Polo Flyff 01/02/2017
Florent Polo "World of Warcraft" 08/09/2019
] (delimiter is spaces)
;
table2:
LOAD
FirstName,
LastName,
FirstSortedValue("Product bought", -"Date of payment") as Product,
Date(Max("Date of payment")) as "Date of payment"
RESIDENT
table1
GROUP BY
FirstName,
LastName
;
DROP TABLE table1;