Question about group by

flo2 — Wed, 15 Dec 2021 10:52:03 GMT

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 !

Re: Question about group by

maxgro — Tue, 16 Nov 2021 19:23:23 GMT

try with FirstSortedValue in the script

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

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;

topic Re: Question about group by in QlikView

Question about group by

Re: Question about group by