topic corresponding attribute related to max record in QlikView

corresponding attribute related to max record

Wed, 15 Dec 2010 21:21:57 GMT

Hello all,

Below is my daily table. From this daily table, I only want to extract the max record based on recnum and all the corresponding data related to the max record into separate table. How do i go about it? Any help will be appreciated.

DAILY TABLE
DATE	recnum	STATUS	ID	STATUS_BHANGE
11/3/2010	199701	A	1	A TO B
11/3/2010	199702	B	1	A TO B
11/3/2010	200711	B	1	B TO A
11/3/2010	200712	A	1	B TO A
11/23/2010	1554971	A	1	A TO B
11/23/2010	1554972	B	1	A TO B
11/23/2010	1563321	B	1	B TO A
11/23/2010	1563322	A	1	B TO A

MAX TABLE
DATE	recnum	STATUS	ID	STATUS_BHANGE
11/23/2010	1563322	A	1	B TO A

corresponding attribute related to max record

Wed, 15 Dec 2010 21:37:03 GMT

Hi there

I would use max(num(left(recnum,6)))

This way you only take out the number (if the recnum always starts with 6 digits, like if it is year/month) and parses it to a number which the max function can handle.

//John

corresponding attribute related to max record

Wed, 15 Dec 2010 21:55:17 GMT

Hi John,

Sorry, the table did not post cleanly. I am not looking to parse the record.

Here is the simple explanation

max(recnum) group by ID will return recnum='1563322'

My max table should contain all attribute related to recnum 1563322

Hope this explains.

Thank you.

Pranita

corresponding attribute related to max record

Anonymous — Thu, 16 Dec 2010 02:38:24 GMT

Hi Pranita,

You could sort the table by recnum, descending, and then just do a resident load and load the first row only from the sorted table.