topic Re: Trying to pull most recent record by date in QlikView

Trying to pull most recent record by date

Fri, 15 May 2015 17:37:20 GMT

I have a recordset which looks like this.

Request ID Record Created Date Name

1 2/5/2012 AAA

1 5/15/2013 AAA

1 4/3/2014 AAA

2 3/3/2014 BBB

3 6/25/2013 CCC

3 8/2/2014 CCC

I need the resulting table to look like this

Request ID Record Created Date Name

1 4/3/2014 AAA

2 3/3/2014 BBB

3 8/2/2014 CCC

I have tried numerous things to no avail. I would like to use in both a straight table and in the load script.

Any suggestions?

Re: Trying to pull most recent record by date

Gysbert_Wassenaar — Mon, 18 May 2015 16:58:12 GMT

Create a straight table with Request ID as dimension and two expressions:

max([Record Created Date])
firstsortedvalue(Name, -[Record Created Date])

If you want to do this in the script you can use the same functions:

Summary:

LOAD

[Request ID],

max([Record Created Date]) as MaxRCD,

firstsortedvalue(Name, -[Record Created Date]) as NameMaxRCD

FROM ...

GROUP BY [Request ID];

Re: Trying to pull most recent record by date

Mon, 18 May 2015 20:01:00 GMT

Thank you. I took this as the base and have been using this same thing with other fields in my table that are causing duplicate rows and it is working to pull only the most recent record created.

Re: Trying to pull most recent record by date

kfloresc — Mon, 21 Sep 2020 18:23:11 GMT

I'm getting an effort right before the FROM.

SQL SELECT ....

...

FROM ...

how do I edit the Select portion?