topic Re: How to use Max function int this case in QlikView

How to use Max function int this case

Thu, 15 Aug 2013 14:58:09 GMT

Hi,

I have three fields- ID, Date and code each ID has multiple date and respective multiple codes. The way i am trying to do this is i need to create a filter which just says (1,0) where if it's "1" then there is ID with a most recent date and respective code, if "0" default it to the way it is where ID has multiple dates and respective codes.

I have tried to create a different table and concatenate or join back to the actual table no nothing worked for me, hope that some one can help me with this?

ID	Date	Code
12894	1/22/2013	acedr
12894	1/23/2013	teyhcu
12894	1/24/2013	sheyc
12894	1/25/2013	uieldo
19028	2/16/2013	odleif
19028	2/17/2013	iepdls
19028	2/18/2013	qpalif
19028	2/19/2013	oslpar

Re: How to use Max function int this case

swuehl — Thu, 15 Aug 2013 15:08:26 GMT

Maybe like

Table1:

LOAD ID, Date, Code FROM ...;

LEFT JOIN LOAD

ID,

max(Date) as Date,

1 as MaxDate

RESIDENT Table1

GROUP BY ID;

Your Date should be recognized as Date by QV, having a numeric representation. Set your DateFormat variable accordingly, if needed.

edit: Selecting 1 from field MaxDate should limit your records to the latest codes then, not selecting anything in field MaxDate should show all records.

Re: How to use Max function int this case

Thu, 15 Aug 2013 15:27:02 GMT

This definetly makes sense Swuehl, I think this should give me the desired result but right now i am still doing some testing on this. But thanks for the quick response.

Re: How to use Max function int this case

Fri, 16 Aug 2013 13:43:50 GMT

Exact result that i needed.