topic Re: Get Max date by second column in QlikView

Get Max date by second column

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

Hi All,

Kindly help me to get the max date value using doc name

User Name	Doc_name	Last used
A	Apple	28/11/2016
A	Apple	13/02/2017
A	Apple	14/01/2017
A	Ball	02/02/2017
B	Apple	26/01/2017
B	Apple	10/02/2017
B	Apple	21/02/2017
B	Ball	05/12/2016
B	Ball	08/02/2017
C	Apple	06/12/2016
C	Apple	16/01/2017
C	Apple	01/02/2017
C	Ball	06/02/2017
C	Ball	07/11/2016
C	Ball	03/02/2017
C	Ball	14/11/2016
C	Ball	18/11/2016

Thank you

Re: Get Max date by second column

its_anandrjs — Thu, 24 Aug 2017 13:28:58 GMT

Try with

Tab1:

Load

[User Name],

Doc_name,

[Last used]

From Location

Left Join(Tab1)

Load

Doc_name,

Date(MAX([Last used])) as MasDate

Resident Tab1

Group By Doc_name;

Re: Get Max date by second column

eromiyasimon — Thu, 24 Aug 2017 13:30:56 GMT

Hi, thank you for the reply can you please help me to do in expression

Re: Get Max date by second column

its_anandrjs — Thu, 24 Aug 2017 13:34:18 GMT

Hope you looking for this

Tab1:

LOAD * Inline

[

User Name, Doc_name, Last used

A, Apple, 28/11/2016

A, Apple, 13/02/2017

A, Apple, 14/01/2017

A, Ball, 02/02/2017

B, Apple, 26/01/2017

B, Apple, 10/02/2017

B, Apple, 21/02/2017

B, Ball, 05/12/2016

B, Ball, 08/02/2017

C, Apple, 06/12/2016

C, Apple, 16/01/2017

C, Apple, 01/02/2017

C, Ball, 06/02/2017

C, Ball, 07/11/2016

C, Ball, 03/02/2017

C, Ball, 14/11/2016

C, Ball, 18/11/2016

];

Left Join(Tab1)

LOAD

Doc_name,

Date(Max([Last used])) as MaxDate

Resident Tab1

Group By Doc_name

Order By Doc_name;

Find the attached also

Re: Get Max date by second column

eromiyasimon — Thu, 24 Aug 2017 13:36:53 GMT

no i want to use inside a chart as expression

Re: Get Max date by second column

its_anandrjs — Thu, 24 Aug 2017 13:37:50 GMT

Write Expression like

=Aggr( Date(Max([Last used])),Doc_name)

See attached

Re: Get Max date by second column

anders_thorngaa — Thu, 24 Aug 2017 14:36:14 GMT

I'm not sure what result you are looking for.

I suppose you load User name, Doc_name and Last used as inline table. You can then create a table with Doc_name as dimension and expression = Max([Last used])

Re: Get Max date by second column

effinty2112 — Thu, 24 Aug 2017 15:26:06 GMT

Hi Eromiya,

Maybe:

Doc_name	Date(Max([Last used]))
Apple	21/02/2017
Ball	08/02/2017

cheers

Andrew