Pivot table - supress column when null, keep row

[lucasdavis500]

I have a pivot table with 3 dimensions, Name, Type, Date. I think have some expression count().

My issue is that there are some analysts, 'Name', that do not have a 'count' for the date, or type, due to being out of office, etc..

The table looks like this..

where George, Lane and Tara do not have a value. However, I would like their names to remain in my table, but the column '-' to go away. I think I have tried every variation of 'suppress null values' and 'show all values' that there is possible with these 3 dimensions and one expression. Is there a way I can keep the names of the analysts, but get rid of the 'Null' date column? Everything I've tried has amounted to George, Lane and Tara disappearing along with the Null column.

This must be a common problem for many qlik developers, and apologizies if it's already been solutioned, but I cannot seem to find a similar scenario...

[vishsaggi]

Can you share a sample to look into?

[lucasdavis500]

Hi Vishwarath, I cannot share sample QVD as this is very sensitive info. I can, however, give a sample of the data. Below is a sample of what my input data looks like. I then transform this to stack my dates and create a 'Canonical' Date, where I can use DateType in set analysis. I think the issue here lies with the fact that not all DateRes are populated. Also, in this time frame George, for example, is not there, because he is out of office. So, when I tell QV to show me all values, it is viewing this as George (Who wasn't in) resolved 0 values on a NULL date (date doesn't exist because he wasn't here to work any 'Types', but I would like to show that on the days he wasn't here he worked 0). I think this is why there is '-' ,or NULL, for Mon/Tue, but for the column '-', there is '0', because George's name is populated 0 times on a null date. I hope this makes sense... I'm just not sure how to resolve it..

Name	DateCr	DateRes	Type
Tim	1/1/2017	1/3/2017	DCMD
Bob	1/2/2017		VAL
Kim	1/3/2017	1/5/2017	CCMR
Jim	1/4/2017	1/8/2017	NMA
Laura	1/5/2017		NL

Name	CanonicalDate	Type	DateType
Tim	1/1/2017	DCMD	DateCr
Tim	1/3/2017	DCMD	DateRes
Bob	1/2/2017	VAL	DateCr
Kim	1/3/2017	CCMR	DateCr
Kim	1/5/2017	CCMR	DateRes
Jim	1/4/2017	NMA	DateCr
Jim	1/8/2017	NMA	DateRes
Laura	1/5/2017	NL	DateCr

[lucasdavis500]

Hi V, I have attached a sample qvw with a similar problem... I want to eliminate NULL Column and show 0's for the columns with date...

[vishsaggi]

Sorry got stuck with something yesterday and could not reply. Sure thanks for sharing the sample will look into it.

[lucasdavis500]

UPDATE: I fixed the Subtotals issue. I now can see my analysts even on days where they are absent, and have 0 values populated in their cells. I also have subtotals for analysts who were working.

My concern: Performance. I am very concerned this is going to have poor performance in production side. There are about 12 nested IF conditions (each with two ISNULL() functions), plus two count conditions as the end else if....

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hi V, I think I have solved this by doing - IF(ISNULL(CanonicalWeekDay) AND Name = 'George', 0, IF(....), COUNT(TYPE))))))

Where I am forcing QV to take 0's instead of null values, and specifying in IF statement the name of each analyst  (in case they take off of work). This has eliminated my extra column, but populated 0's for days with missing data, but I am concerned about performance in Production server... I'm sure this can't be the only solution... This also has eliminated my horizontal subtotals which I would like to keep..

the '0' subtotals to the right only happen when I use the IF(ISNULL(CanonicalWeekDay) And Name = 'Name', 0) function on that particular employee. If I remove it, it shows their subtotals, but I won't be able to accommodate for them if they are absent in the future..

[vishsaggi]

Oh sounds good. you figured it out. So why cant you use some of those if  statements in your script level, I am not entirely sure about your IF statements so just a thought that you can create some flags in your script and use those flags in your pivot expression.

[Peter_Cammaert]

Usually performance of expressions containing IF() functions will go south if you include IF() calls on the inside of the aggregation function (e.g. in the parameter expression), not if the IF() call is made to contain the aggregation function. Even if you have hundreds of cells in your pivot table.

If you really think that the IF() will negatively impact performance, you can try something else. What do you get if you use an expression like:

=RangeSum(0, Count(TYPE))

Peter