Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

lucasdavis500
Contributor II

Pivot table - supress column when null, keep row

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

chart.png

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

7 Replies
vishsaggi
Esteemed Contributor III

Re: Pivot table - supress column when null, keep row

Can you share a sample to look into?

Highlighted
lucasdavis500
Contributor II

Re: Pivot table - supress column when null, keep row

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

   

NameDateCrDateResType
Tim1/1/20171/3/2017DCMD
Bob1/2/2017VAL
Kim1/3/20171/5/2017CCMR
Jim1/4/20171/8/2017NMA
Laura1/5/2017

NL

   

NameCanonicalDateTypeDateType
Tim1/1/2017DCMDDateCr
Tim1/3/2017DCMDDateRes
Bob1/2/2017VALDateCr
Kim1/3/2017CCMRDateCr
Kim1/5/2017CCMRDateRes
Jim1/4/2017NMADateCr
Jim1/8/2017NMADateRes
Laura1/5/2017NLDateCr
lucasdavis500
Contributor II

Re: Pivot table - supress column when null, keep row

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

Re: Pivot table - supress column when null, keep row

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

lucasdavis500
Contributor II

Re: Pivot table - supress column when null, keep row

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

update.png

vishsaggi
Esteemed Contributor III

Re: Pivot table - supress column when null, keep row

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.

Re: Pivot table - supress column when null, keep row

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