topic Re: hide column when it's all 0 in QlikView

hide column when it's all 0

yelin_nyu — Sun, 25 Aug 2013 10:12:24 GMT

I have a straight table look like below

product Global qty International qty NA qty EMEA qty APAC qty

A 1000 700 300 0 700

Region is not in dimension becaue i need to show global and internation qty on the same page as regional breakdown. i used set analysis/if statement in each expression to specify the region (ie. region=EMEA, region<>NA,etc)

suppress zero in properties would not work in this case, tried that already.

I have a condition to show each region when it's selected. substringcount(concat(region, '|'), 'NA'), but I want to auto hide columns (EMEA) where the expression is 0 or null when i don't make any selection.

Re: hide column when it's all 0

yelin_nyu — Sun, 25 Aug 2013 10:17:55 GMT

another thing i want to add, i am looking for something simple. I know i can potentially do if( substringcount(concat(region, '|'), 'NA')>0 or expression>0,1,0)

but the actual expressions are really really longggggg, plus the actual table has 50 expressions, i have take performance into consideration. and my expression name is also dynamic, depending on few conditions it will change accordingly

Re: hide column when it's all 0

orital81 — Sun, 25 Aug 2013 12:06:14 GMT

Hi Lin Ye, please attach your example, it will be easier to help you this way.

Re: hide column when it's all 0

yelin_nyu — Sun, 25 Aug 2013 12:43:17 GMT

product	sum($<{region=>},qty))	sum(if(region<>'NA',qty))	sum(if(region='NA',qty))	sum(if(region='EMEA',qty))	sum(if(region='APAC',qty))
A	1000	700	300	0	700
B	2000	1000	1500	0	500

as you can see emea didn't any qty for product A & B, so i want to hide any column that the expression=0

rememer this table is a simplified of what i really deal with. i can write in condition show/hide if(column(n)>0,1,0)., but when i have 72 expressions in total, this gets really cumbersome and i lose track which column im on. and this will slow down the tool as well. im wondering if there is a quick self referencing expression, like currentcolumn(0 or currentexpression.

Re: hide column when it's all 0

Anonymous — Sun, 25 Aug 2013 13:13:49 GMT

Hi Lin,

have you tried suppressing missing or zero values (under table properties-->presentation)?

hope that helped

Re: hide column when it's all 0

yelin_nyu — Sun, 25 Aug 2013 13:20:19 GMT

yes, notice that my expressions are using if and set analysis, region is not in dimension. so suppress 0 will not work.

Re: hide column when it's all 0

jagan — Sun, 25 Aug 2013 14:47:18 GMT

Hi,

If you are using Qlikview 11, then use Conditional expression so that you can hide the column

Or else use Hide Column option in Presentation tab of Straight Table.

Hope this helps you.

Regards,

Jagan.

Re: hide column when it's all 0

orital81 — Sun, 25 Aug 2013 19:07:05 GMT

Dear Lin, I suggest to use pivot table with Product and Region as dimentions and Qty as expression.

This way you will get a column per Region dynamicaly and 0 values will not be shown (as long as you suppress zero values).

Dim1: Region

Dim 2: Product

Expression: [ Qty ]