Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
flo2
Contributor III
Contributor III

Question about group by

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 !

1 Solution

Accepted Solutions
maxgro
MVP
MVP

try with FirstSortedValue in the script

https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/AggregationF...


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;

View solution in original post

1 Reply
maxgro
MVP
MVP

try with FirstSortedValue in the script

https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/AggregationF...


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;