Get Contract for Max Date

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

Hello,

I have the following table (DATE_CON has format: DD/MM/YYYY):

MALL	UNIT	BRAND	DATE_CON	CONTRACT
MALL1	UNIT01	BRAND1	01/04/2017	35
MALL1	UNIT01	BRAND1	01/05/2017	35
MALL1	UNIT01	BRAND1	01/06/2017	35
MALL1	UNIT01	BRAND1	01/07/2017	35
MALL1	UNIT01	BRAND1	01/08/2017	35
MALL1	UNIT01	BRAND1	01/09/2017	35
MALL1	UNIT01	BRAND1	01/10/2017	35
MALL1	UNIT01	BRAND1	01/11/2017	35
MALL1	UNIT01	BRAND1	01/12/2017	35
MALL1	UNIT11	BRAND1	01/12/2017	90
MALL2	UNIT02	BRAND1	01/06/2017	33
MALL2	UNIT02	BRAND1	01/07/2017	33
MALL2	UNIT02	BRAND1	01/08/2017	33
MALL2	UNIT02	BRAND1	01/09/2017	33
MALL2	UNIT02	BRAND1	01/10/2017	33
MALL2	UNIT02	BRAND1	01/11/2017	33
MALL2	UNIT02	BRAND1	01/12/2017	33
MALL3	UNIT03	BRAND1	01/03/2017	42
MALL3	UNIT03	BRAND1	01/04/2017	42
MALL3	UNIT03	BRAND1	01/05/2017	42
MALL3	UNIT03	BRAND1	01/06/2017	42
MALL3	UNIT03	BRAND1	01/07/2017	42
MALL3	UNIT03	BRAND1	01/08/2017	42
MALL3	UNIT03	BRAND1	01/09/2017	42
MALL3	UNIT03	BRAND1	01/10/2017	42
MALL3	UNIT03	BRAND1	01/11/2017	42
MALL3	UNIT03	BRAND1	01/12/2017	42
MALL3	UNIT33	BRAND1	01/12/2017	48
MALL4	UNIT04	BRAND1	01/02/2017	77
MALL4	UNIT04	BRAND1	01/03/2017	77
MALL4	UNIT04	BRAND1	01/04/2017	77
MALL4	UNIT04	BRAND1	01/05/2017	77
MALL4	UNIT04	BRAND1	01/06/2017	77
MALL4	UNIT04	BRAND1	01/07/2017	77
MALL4	UNIT04	BRAND1	01/08/2017	77
MALL4	UNIT04	BRAND1	01/09/2017	77
MALL4	UNIT04	BRAND1	01/10/2017	77
MALL4	UNIT04	BRAND1	01/11/2017	77
MALL4	UNIT04	BRAND1	01/12/2017	77
MALL5	UNIT05	BRAND1	01/02/2017	65
MALL5	UNIT05	BRAND1	01/03/2017	65
MALL5	UNIT05	BRAND1	01/04/2017	65
MALL5	UNIT05	BRAND1	01/05/2017	65
MALL5	UNIT05	BRAND1	01/06/2017	65
MALL5	UNIT05	BRAND1	01/07/2017	65
MALL5	UNIT05	BRAND1	01/08/2017	65
MALL5	UNIT05	BRAND1	01/09/2017	65
MALL5	UNIT05	BRAND1	01/10/2017	65
MALL5	UNIT05	BRAND1	01/11/2017	65
MALL5	UNIT05	BRAND1	01/12/2017	65
MALL6	UNIT06	BRAND1	01/04/2017	14
MALL6	UNIT06	BRAND1	01/05/2017	14
MALL6	UNIT06	BRAND1	01/06/2017	14
MALL6	UNIT06	BRAND1	01/07/2017	14
MALL6	UNIT06	BRAND1	01/08/2017	14
MALL6	UNIT06	BRAND1	01/09/2017	14
MALL6	UNIT06	BRAND1	01/10/2017	14
MALL6	UNIT06	BRAND1	01/11/2017	14
MALL6	UNIT06	BRAND1	01/12/2017	14
MALL7	UNIT07	BRAND1	01/07/2017	39
MALL7	UNIT07	BRAND1	01/08/2017	39
MALL7	UNIT07	BRAND1	01/09/2017	39
MALL7	UNIT07	BRAND1	01/10/2017	39
MALL7	UNIT07	BRAND1	01/11/2017	39
MALL7	UNIT07	BRAND1	01/12/2017	39
MALL8	UNIT08	BRAND1	01/08/2017	52
MALL8	UNIT08	BRAND1	01/09/2017	52
MALL8	UNIT08	BRAND1	01/10/2017	52
MALL8	UNIT08	BRAND1	01/11/2017	52
MALL8	UNIT08	BRAND1	01/12/2017	52
MALL9	UNIT09	BRAND1	01/04/2015	87
MALL9	UNIT09	BRAND1	01/05/2015	87
MALL9	UNIT09	BRAND1	01/06/2015	87
MALL9	UNIT09	BRAND1	01/07/2015	87
MALL9	UNIT09	BRAND1	01/08/2015	87
MALL9	UNIT09	BRAND1	01/09/2015	87
MALL10	UNIT10	BRAND1	01/12/2010	25
MALL10	UNIT10	BRAND1	01/01/2011	25
MALL10	UNIT10	BRAND1	01/02/2011	25
MALL10	UNIT10	BRAND1	01/03/2011	25
MALL10	UNIT10	BRAND1	01/04/2011	25
MALL10	UNIT10	BRAND1	01/05/2011	25

What I need is to get the last Contract (getting the Max Date) for every Mall and Unit.

Note that there could be more than 1 Unit for a Mall for a Max Date, like Mall 1 or Mall 3.

I should get the following:

MALL	UNIT	BRAND	DATE_CON	CONTRACT
MALL1	UNIT01	BRAND1	01/12/2017	35
MALL1	UNIT11	BRAND1	01/12/2017	90
MALL2	UNIT02	BRAND1	01/12/2017	33
MALL3	UNIT03	BRAND1	01/12/2017	42
MALL3	UNIT33	BRAND1	01/12/2017	48
MALL4	UNIT04	BRAND1	01/12/2017	77
MALL5	UNIT05	BRAND1	01/12/2017	65
MALL6	UNIT06	BRAND1	01/12/2017	14
MALL7	UNIT07	BRAND1	01/12/2017	39
MALL8	UNIT08	BRAND1	01/12/2017	52
MALL9	UNIT09	BRAND1	01/09/2015	87
MALL10	UNIT10	BRAND1	01/05/2011	25

Do you know how could I do that? I'd need to do this as an expression for a Pivot Table.

Thank you!!!

Re: Get Contract for Max Date

YoussefBelloum — Fri, 05 Jan 2018 13:12:58 GMT

Hi,

just use Date(Max(date_field))

see attached

Re: Get Contract for Max Date

Anil_Babu_Samineni — Fri, 05 Jan 2018 13:15:10 GMT

Just use this expression in pivot table

Sum({<DATE_CON = {'$(=Date(Max(DATE_CON)))'}>} CONTRACT)

Re: Get Contract for Max Date

tresB — Fri, 05 Jan 2018 13:20:45 GMT

Exp1=Date(Max(DATE_CON))

Exp2=FirstSortedValue(CONTRACT, -DATE_CON)

Re: Get Contract for Max Date

microwin88x — Fri, 05 Jan 2018 13:36:01 GMT

Hello Anil,

I've tried this, but the problem is that it doesn't shows Malls 9 and 10 in the table.

I believe that happens because Malls 1 to 8 have DATE_CON = 01/12/2017 and Mall 9 has 01/09/2015 and Mall 10 has 01/05/2011.

Do you know how could I fix it?

Thanks!

Re: Get Contract for Max Date

microwin88x — Fri, 05 Jan 2018 13:39:52 GMT

Hello Tresesco,

Is there any way to use a single expression instead of two? Because I wouldn't really need to show the date.

Thanks!

Re: Get Contract for Max Date

tresB — Fri, 05 Jan 2018 13:41:46 GMT

Just omit the first expression. Second expression is not at all dependent on first, because it's not a dimension.

topic Re: Get Contract for Max Date in QlikView

Get Contract for Max Date

Re: Get Contract for Max Date

Re: Get Contract for Max Date

Re: Get Contract for Max Date

Re: Get Contract for Max Date

Re: Get Contract for Max Date

Re: Get Contract for Max Date