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

[danyal_akhlaq]

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:

[sunny_talwar]

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?

[danyal_akhlaq]

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

[sunny_talwar]

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

[danyal_akhlaq]

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

[sunny_talwar]

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

[danyal_akhlaq]

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.

[sunny_talwar]

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. 🙂

[danyal_akhlaq]

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!

[sunny_talwar]

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)