topic Re: Hide columns in table where fields are empty/null in QlikView

Hide columns in table where fields are empty/null

Fri, 06 Jun 2014 09:44:34 GMT

Hello Community,

In a table box or chart I want to display the product number with around 30 fields of characteristics of each product.

ProductNo	Char 1	Char 2	Char 3	Char 5	Char 6	Char 7	Char 8	Char 9
ABC	2,31	-	99	-	77,21	91	-	T
XYZ	-	-	-	-	-	-	-	-

My goal is to only show products where at least one of those characteristics is not null or empty and hide products with no characteristics at all.

So in above example how can I hide the row XYZ?

I'm guessing I need to prepare the data somehow but I don't have a clue yet.

Hope you have some ideas.

Re: Hide columns in table where fields are empty/null

senpradip007 — Fri, 06 Jun 2014 09:49:48 GMT

In Dimensions tab check "Suppress When Value is Null" you will get your desire result.

Re: Hide columns in table where fields are empty/null

CELAMBARASAN — Fri, 06 Jun 2014 09:51:34 GMT

Upload a Sample.

In charts it can be done. By using Suppress when value is null in the Dimension tab for the ProductNo dimension.

Re: Hide columns in table where fields are empty/null

Fri, 06 Jun 2014 10:16:17 GMT

By using Suppress when value is null all other columns are also gone.

For example if column8 is null but column9 is not then I won't see this row.

Re: Hide columns in table where fields are empty/null

Jason_Michaelides — Fri, 06 Jun 2014 10:20:22 GMT

Maybe add an expression using RangeSum:

RangeSum(Char1,Char2,Char3,Char4,Char5,Char6,Char7,Char8,etc)

This will evaluate to zero in the chart then it'll be hidden by default. You can hide the expression in the Presentation tab so as not to clutter the chart.

I'd do this in the script if possible...

Hope this helps,

Jason

Re: Hide columns in table where fields are empty/null

Fri, 06 Jun 2014 10:39:57 GMT

Thanks Jason that works. However the chart became really slow (at least 3 seconds for scrolling) even after putting the statement in the load script. There are 1300 rows in total and only 130 are being displayed.

In the chart expression I put the field ExisitingChars and hide the column.

Is there a better way to make the chart become quicker again?

Re: Hide columns in table where fields are empty/null

Thu, 12 Jun 2014 08:50:21 GMT

so I ended up using:

if(RangeSum(Char1,Char2,Char3,Char4,Char5,Char6,Char7,Char8)) <> 0,1,0) AS ExisitingChars

in the load stmt.

This makes the chart responsive again.

Re: Hide columns in table where fields are empty/null

chaper — Thu, 13 Nov 2014 15:23:44 GMT

Hi Jason,

How to hide columns when there is no value after makeing selection??

I want to hide Char 2 Char5 char 8 as seen above.

Thanks

Re: Hide columns in table where fields are empty/null

Jason_Michaelides — Thu, 13 Nov 2014 17:02:38 GMT

Maybe use the conditional expression functionality. (Or conditional dimension if Char1 etc are dimensions). Use IsNull(Char2)=0

Hope this helps,

Jason

Re: Hide columns in table where fields are empty/null

chaper — Thu, 13 Nov 2014 17:58:31 GMT

Thanks Jason