topic Select the latest dated record for each group in QlikView

Select the latest dated record for each group

Wed, 12 Oct 2011 17:54:13 GMT

I have an example QVW attached. I am trying to create a variable expression to count a number of "assessments" However each "resident" has multiple "assessments", I only want to count the one for each resident with the most current date. I have tried using the max function and firstsortedvalue but it is not working.

Resident1 AssessmentA1 1/1/2011

Resident1 AssessmentB1 4/1/2011

Resident2 AssessmentA2 4/1/2011

Resident2 AssessmentB2 1/1/2011

In this case I would only have a count of 2 for AssessmentB1 and AssessmentA2.

Re: Select the latest dated record for each group

Wed, 12 Oct 2011 18:15:16 GMT

Check out the attachment. I added a new expression.

Regards,

Kiran.

Re: Select the latest dated record for each group

Wed, 12 Oct 2011 18:31:43 GMT

That looks great in the chart, however, when I tried to create a count of those assessments outside of the chart, it is not working. I updated the attachment.

Select the latest dated record for each group

Wed, 12 Oct 2011 18:38:26 GMT

If your expected result is 3 then try below expression in the textbox:

=sum(aggr(if(ARD_Reference_Calendar=date(MAX(total <ResidentID> ARD_Reference_Calendar)),1,0),ResidentID,ARD_Reference_Calendar))

Works for me

-Kiran.

Select the latest dated record for each group

Wed, 12 Oct 2011 20:06:42 GMT

That did work but here is a concern. Is this saying that the date of the record has to be equal to the max date of all the records? So if I were to change my example to be:

Resident1 AssessmentA1 1/1/2011

Resident1 AssessmentB1 3/1/2011

Resident2 AssessmentA2 4/1/2011

Resident2 AssessmentB2 1/1/2011

I would still want a count of 2; AssessmentB1 and AssessmentA2. I will also likely need to to add other parameters into my expression to filter down data even more. I can't figure out where to fit these in.

Select the latest dated record for each group

Thu, 13 Oct 2011 02:18:09 GMT

It does work that way. Let us know what exactly you want in the chart.

Regards,

Kiran.

Re: Select the latest dated record for each group

Thu, 13 Oct 2011 13:04:27 GMT

Date Parameter1 Parameter2

Resident1 AssessmentA1 1/1/2011 X A

Resident1 AssessmentB1 3/1/2011 Y B

Resident2 AssessmentA2 4/1/2011 X A

Resident2 AssessmentB2 1/1/2011 Y A

Resident3 AssessmentA3 2/1/2011 Y B

Resident3 AssessmentB3 5/1/2011 X A

Resident4 AssessmentA4 3/1/2011 X B

Resident5 AssessmentA5 1/1/2011 X A

I need to count one assessment per resident being the most recent assessment per resident and where parameter1=X and parameter2=A.

The result with the above example should be 3: assessmentA2, B3, and A5.

Thanks in advance!

Re: Select the latest dated record for each group

Thu, 13 Oct 2011 17:45:59 GMT

Loaded your data and got the result. Hopefully I understand your requirement.

Regards,

Kiran.