Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
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;