topic MaxString() all values including Null with Aggr() function in QlikView

MaxString() all values including Null with Aggr() function

danyal_akhlaq — Sat, 16 Nov 2024 04:47:03 GMT

Hi All,

Over View:

Using Chart Object,

Dimensions: (Store, StoreName, Category)

Only Expression (using Aggr() function)

I have a table in QlikView and I am aggregating a column (Proposition) in that table based on all possible dimensions but the problem is that MaxString() function is not returning the Null values in that table.

MaxString is removing the Categories (Cubes and Noodles) as these two categories have no data, but i need them too.

Expression:

=MaxString(aggr(MaxString(Proposition), Month,Store, StoreName, Address, Store_Type, Category,Proposition))

DataTable:

Re: MaxString() all values including Null with Aggr() function

sunny_talwar — Fri, 11 Jan 2019 12:20:21 GMT

I am confused, in your post you mentioned that there are no dimensions, but in your image you show a chart with 3 dimensions? Would you be able to clarify?

Re: MaxString() all values including Null with Aggr() function

danyal_akhlaq — Fri, 11 Jan 2019 12:45:25 GMT

Oops! my mistake now i have updated the question, using three dimensions.

Re: MaxString() all values including Null with Aggr() function

sunny_talwar — Fri, 11 Jan 2019 12:59:01 GMT

Seems like this is in QlikView, right? Have you tried to uncheck 'Suppress Zero-Values' on the Presentation tab of the chart properties?

Re: MaxString() all values including Null with Aggr() function

danyal_akhlaq — Fri, 11 Jan 2019 13:31:16 GMT

yes it is in QlikView and 'Suppress Zero-Values' is already unchecked.

Re: MaxString() all values including Null with Aggr() function

sunny_talwar — Fri, 11 Jan 2019 13:34:58 GMT

Would you be able to share a sample where we can see the issue?

Re: MaxString() all values including Null with Aggr() function

danyal_akhlaq — Fri, 11 Jan 2019 18:58:01 GMT

Sir,
I have posted DataTable and Expression as sample and the actual problem is that it is not calculating NULL values in the Proposition column.
All data and colunms are sample in this post as i can not post the actual data.

Re: MaxString() all values including Null with Aggr() function

sunny_talwar — Fri, 11 Jan 2019 18:59:21 GMT

Please provide the Excel version of the data posted above. I have no way to load an image as date, neither do I have the time to type that all up in Excel or INLINE. 🙂

Re: MaxString() all values including Null with Aggr() function

danyal_akhlaq — Sat, 12 Jan 2019 07:37:57 GMT

Dear Sir,

I have added data file and qvd both for your testing, i just need the Store-1 to show all the data with null value as it does not have data of two categories (cubes and noodles).

Thanks!

Re: MaxString() all values including Null with Aggr() function

sunny_talwar — Mon, 14 Jan 2019 12:49:43 GMT

Script

Main:
LOAD Text(Date(Month,'MM-YYYY')) as Month,
	 ROWID,
	 StoreCode,
	 Store,
	 Store_Type,
	 Region,
	 Channel
FROM Data.xlsx
(ooxml, embedded labels, table is Main);

Left Join (Main)
LOAD Text(Date(Month,'MM-YYYY')) as  Month,
	 StoreCode,
	 Category,
	 Prop
FROM Data.xlsx
(ooxml, embedded labels, table is Prop);

FinalMain:
LOAD Month&'|'&StoreCode&'|'&Category as Key,
	 *
Resident Main;

Temp:
LOAD DISTINCT StoreCode,
	 Store,
	 Store_Type,
	 Region,
	 Channel
Resident Main;

Join (Temp)
LOAD DISTINCT Text(Date(Month,'MM-YYYY')) as  Month,
	 Category
FROM Data.xlsx
(ooxml, embedded labels, table is Prop);

Concatenate (FinalMain)
LOAD *
Resident Temp
Where not Exists (Key, Month&'|'&StoreCode&'|'&Category);

DROP Tables Temp, Main;
DROP Field Key;
RENAME Table FinalMain to Main;

STORE Main into Main.qvd(qvd);

DROP Table Main;

PropHurdle:
Mapping
LOAD Text(Date(Month,'MM-YYYY'))&'-'&Region&'-'&Store_Type&'-'&Channel&'-'&Category as LookUpKey,
     PropHurdle
FROM Data.xlsx
(ooxml, embedded labels, table is PropHurdle);

DataTable:
LOAD Month,
	 ROWID,
	 StoreCode,
	 Store,
	 Store_Type,
	 Region,
	 Channel,
	 Category,
	 Prop,
     ApplyMap('PropHurdle',Month&'-'&Region&'-'&Store_Type&'-'&Channel&'-'&Category,'Not Defined') as PropHurdle
FROM Main.qvd (qvd);

and then use the expression

MaxString(Prop)