Last Row with value not null (Set Analysis)

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

Hello,

I have the following table for a particular Client = 300 (note that dates have format DD/MM/YYYY):

MALL	CLIENT	UNIT	CONTRACT	CONTRACT_FROM	CONTRACT_TO	AREA	CAL_DATE
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/10/2017
MALL01	300	U008	503	08/02/2018	09/03/2022	-	01/11/2017

I have Dimensions Mall, Client and Unit, and then all Expressions like Contract, Contract_From, Contract_To and Area.

The following Expression gets the last Contract for the last Date (Cal_Date) for each Mall/Client/Unit:

=MAX(IF(AGGR(NODISTINCT MAX(CAL_DATE),MALL)=CAL_DATE,CONTRACT))

It works fine, but the thing is that this case, for the last Cal_Date (01/11/2017) has Area = NULL and I'd need to modify the Expression in order to get those Contracts whose Area is not null for the last Cal_Date and Contract_From is not null too. In this case, I should get the first row of the table with Contract = 500.

Do you know how could I do that?

---

Here's the table with my data:

MALL	CLIENT	UNIT	CONTRACT	CONTRACT_FROM	CONTRACT_TO	AREA	CAL_DATE
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/01/2017
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/01/2017
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/02/2017
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/02/2017
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/03/2017
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/03/2017
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/04/2017
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/05/2017
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/06/2017
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/07/2017
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/08/2017
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/09/2017
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/10/2017
MALL01	300	U008	503	08/02/2018	09/03/2022	-	01/11/2017
MALL02	300	U010	800	18/12/2014	17/12/2018	250	01/01/2017
MALL02	300	U010	800	18/12/2014	17/12/2018	250	01/02/2017
MALL02	300	U010	800	18/12/2014	17/12/2018	250	01/03/2017
MALL02	300	U010	800	18/12/2014	17/12/2018	250	01/04/2017
MALL02	300	U010	800	18/12/2014	17/12/2018	250	01/05/2017
MALL02	300	U010	800	18/12/2014	17/12/2018	250	01/06/2017
MALL02	300	U010	800	18/12/2014	17/12/2018	250	01/07/2017
MALL02	300	U010	800	18/12/2014	17/12/2018	250	01/08/2017
MALL02	300	U010	800	18/12/2014	17/12/2018	250	01/09/2017
MALL02	300	U010	800	18/12/2014	17/12/2018	250	01/10/2017
MALL02	300	U010	800	18/12/2014	17/12/2018	250	01/11/2017
MALL02	300	U010	800	18/12/2014	17/12/2018	250	01/12/2017
MALL03	525	U240	900	23/10/2015	-	210	01/01/2017
MALL03	525	U240	900	23/10/2015	01/01/2017	210	01/02/2017
MALL03	525	U240	900	23/10/2015	01/02/2017	210	01/03/2017
MALL03	525	U240	900	23/10/2015	01/03/2017	210	01/04/2017
MALL03	525	U240	900	23/10/2015	01/04/2017	210	01/05/2017
MALL03	525	U240	900	23/10/2015	01/05/2017	210	01/06/2017
MALL03	525	U240	900	23/10/2015	01/06/2017	210	01/07/2017
MALL03	525	U240	900	23/10/2015	01/07/2017	210	01/08/2017
MALL03	525	U240	900	23/10/2015	01/08/2017	210	01/09/2017
MALL03	525	U240	900	23/10/2015	01/09/2017	210	01/10/2017
MALL03	525	U240	900	23/10/2015	01/10/2017	210	01/11/2017
MALL03	525	U240	900	23/10/2015	01/11/2017	210	01/12/2017
MALL03	525	U240	950	-	-	-	01/12/2017

So I need to show a table with Dimensions Mall, Client and Unit with Expressions Contract, Contract_From, Contract_To and Area with the following data (rows in bold😞

MALL	CLIENT	UNIT	CONTRACT	CONTRACT_FROM	CONTRACT_TO	AREA	CAL_DATE	Notes
MALL01	300	U006	500	25/01/2017	23/07/2017	195	01/10/2017	Show this row
MALL01	300	U008	503	08/02/2018	09/03/2022	-	01/11/2017	No because Area is Null
MALL02	300	U010	800	18/12/2014	17/12/2018	250	01/12/2017	Show this row
MALL03	525	U240	900	23/10/2015	01/11/2017	210	01/12/2017	Show this row
MALL03	525	U240	950	-	-	-	01/12/2017	No because Contract and Area is Null

The current Expressions I have are the following, for example to get the last Contract:

=MAX(IF(AGGR(NODISTINCT MAX(CAL_DATE),MALL)=CAL_DATE,CONTRACT))

** Note that I always should select first 1 CLIENT from a Listbox in order to show that table **

Thanks!

Re: Last Row with value not null (Set Analysis)

jonathandienst — Fri, 12 Jan 2018 04:46:26 GMT

Like this, perhaps:

=MAX(IF(AGGR(NODISTINCT MAX({<AREA = {'*'}, CONTRACT = {'*'}, CONTRACT_FROM = {'*'}>} CAL_DATE),MALL)=CAL_DATE,CONTRACT))

'*' will return all non-null values.

topic Last Row with value not null (Set Analysis) in QlikView

Last Row with value not null (Set Analysis)

Re: Last Row with value not null (Set Analysis)