Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Highlighted
danyal_akhlaq
New Contributor III

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

Hi All,

Over View:

Using Chart Object,

Dimensions: (StoreStoreName, 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:Aggr with MaxString.png

Labels (3)
9 Replies

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

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
New Contributor III

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

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

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

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
New Contributor III

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

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

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

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

danyal_akhlaq
New Contributor III

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

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

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. Smiley Happy

danyal_akhlaq
New Contributor III

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

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

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)

image.png