topic getting latest record in script in QlikView

getting latest record in script

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

i am trying to create a flag for the latest record in the script;

I have an email address and Date, i want to flag the row with the latest date per email address.

i have attached an example, my real data has a lot more fields but Email is the key to the rest of the tables and its the date i need it sorted on.

can anyone help please?

Re: getting latest record in script

swuehl — Wed, 21 Dec 2016 11:27:35 GMT

Maybe like

Data:

Load * inline [

Email, Date

PersonA@Email.com, 01/01/2012

PersonA@Email.com, 01/02/2012

PersonA@Email.com, 01/05/2012

PersonA@Email.com, 01/09/2012

PersonB@Email.com, 01/01/2013

PersonB@Email.com, 01/08/2015

PersonB@Email.com, 01/04/2016

]

;

LEFT JOIN

LOAD Email, Maxstring(Date) as Date, 1 as Flag

RESIDENT Data

GROUP by Email;

Re: getting latest record in script

jonathandienst — Wed, 21 Dec 2016 11:27:47 GMT

Data:

Load * inline [

Email, Date

PersonA@Email.com, 01/01/2012

PersonA@Email.com, 01/02/2012

PersonA@Email.com, 01/05/2012

PersonA@Email.com, 01/09/2012

PersonB@Email.com, 01/01/2013

PersonB@Email.com, 01/08/2015

PersonB@Email.com, 01/04/2016

]

;

Join (Data)

LOAD Email,

Max(Date) as Date,

1 as MaxFlag

Resident Data

Group By Email

;

Re: getting latest record in script

hopkinsc — Wed, 21 Dec 2016 11:36:48 GMT

Thanks Stefan, i didn't think of doing something as simple as that, i have been trying to get it working using FirstSortedValue or applymap!

thanks!