topic Re: Using values of hidden column in QlikView

Using values of hidden column

Mon, 27 Apr 2015 19:43:04 GMT

Hello everyone.

So, I have a straight table with a hidden column that can be shown just by clicking a button.

My problem is: is there a way to continue using the values of that column when she is hidden? Because I'm using column(1) when she is being shown so I can use her values, but when I hide her, the column(1) changes to another column.

Is there a way to do that?

Thanks

Re: Using values of hidden column

JonnyPoole — Mon, 27 Apr 2015 19:54:22 GMT

you could create a variable to store the expression and invoke the variable on the original chart expression as well as in the secondary chart expression (where you currently use column(1) )

Re: Using values of hidden column

sunny_talwar — Mon, 27 Apr 2015 19:54:45 GMT

A column hidden by Conditional expression cannot be referenced when its hidden. If you are never going to see it then you can hide it from the presentation tab (but this cannot be controlled by a button) which I think can then be referenced.

HTH

Best,

Sunny

Re: Using values of hidden column

petter — Mon, 27 Apr 2015 19:56:05 GMT

You can refer to the column by name ... the name is the label of the column (expression).

If the label is Quantity on hand then you can refer to it with [Quantity on hand]

This applies to hidden expressions that are hidden explicity or by condition - both can be referred to by name

Re: Using values of hidden column

Anonymous — Mon, 27 Apr 2015 19:58:57 GMT

I use hidden columns all the time. Just label the column and use the label in the expression you are trying to calculate rather than using column numbers.

Re: Using values of hidden column

JonnyPoole — Mon, 27 Apr 2015 20:09:49 GMT

Open question ?

... I wasn't sure that applies to a conditional hide (as shown below) which is available on most charts .

I think it does apply for straight table / presentation tab hide fields ...

anything i'm missing ?

Re: Using values of hidden column

Mon, 27 Apr 2015 20:18:09 GMT

Thanks everyone for the quick answers.

Some points I think I should've explained better.

So, I have this table with these columns: Product (my dimension), Stores(my hidden column), count(Stores), avg(Sales1), avg(Sales2). When I expand the table, my hidden column appears and I can see the both avg of each product store by store, so a quick example:

product store count(store) avg(sales1) avg(sales2)

prod1 2 1 30 32

prod1 5 1 33 29

prod1 4 1 31 30

When I hide the column store, I'm not able to use 2, 5 and 4, even if I just use my expression name (which is Store), or [Store], I didn't found any way to use her.

I tried every tip you guys gave me, but I don't know why I can't use her, maybe what sunindia said is right, because I'm using a Conditional expression to hide that colum (which is also a expression).

I tried to use set analysis with column() or the name of my expression (Store, or [Store]), but I didn't manage to get it done. Is there a way to do that? I mean, use column() or expression name with set analysis?

I hope I'm more clear now and again, thanks everyone.

Re: Using values of hidden column

petter — Mon, 27 Apr 2015 20:28:17 GMT

I just tested it again and I have done this before too ... it definitely works for me ... referencing a hidden column.

Re: Using values of hidden column

sunny_talwar — Mon, 27 Apr 2015 20:29:25 GMT

Somehow it never worked for me. Can you post the test file?

Re: Using values of hidden column

Mon, 27 Apr 2015 20:45:57 GMT

I created an expression to try and count my column, and even a simply count(Store) works for me.

I'm in the same boat as sunindia.

Re: Using values of hidden column

sunny_talwar — Mon, 27 Apr 2015 20:56:11 GMT

Petter Skjolden‌, please check this, this isn't working (PFA)

Best,

Sunny

Re: Using values of hidden column

sunny_talwar — Mon, 27 Apr 2015 20:59:48 GMT

abbaldan‌try and see if this helps? Created two Sum columns (one is conditional) and the other is hidden from the presentation tab. You can use the hidden from the presentation tab to refer in your other expression.

May not be ideal, but it works.

Best,

Sunny

Re: Using values of hidden column

petter — Mon, 27 Apr 2015 21:14:02 GMT

ACTUALLY GUYS ...

There is some confusion here since we are referring to two different ways of hiding a column:

1) Hide the Expression in the Expression Tab with a Conditional

2) Hide the Column in the Presentation Tab with a Conditional

#1 will not let you refer to anything while it is actually not hidden - but removed as a calculation completely

#2 will let you refer to it since it is only hidden from view but still calculated. So referring to it by name is still possible

Re: Using values of hidden column

Tue, 28 Apr 2015 11:57:52 GMT

Well, I tried switching my conditional from expression tab to presentation tab, and I still can't use the hidden column, I dont know why

Re: Using values of hidden column

sunny_talwar — Tue, 28 Apr 2015 12:03:16 GMT

Did you look at the sample qvw I posted above? Re: Using values of hidden column

Re: Using values of hidden column

Tue, 28 Apr 2015 12:12:30 GMT

Yes, I tried to replicate your logic but still didn't work for me. One thing that I noticed is when I try to use my expression name, the name isn't blue like always is when you refeer to a expression by name, when I refeer my hidden column by name, the name is red, [Area] -> Area is red and not blue.

Maybe there is a problem and I'm not noticing, I dont know.

Re: Using values of hidden column

sunny_talwar — Tue, 28 Apr 2015 12:16:22 GMT

Do you have a field name which has the same name as the label? If you do then instead of picking the label name, QV will pick the field name. To avoid this, you can do two things:

Rename your label to something like Area! and then use this in your next expression [Area!]
Use the Column() function instead of the label name (but this could get tricky when you have conditional hide and show for columns above the one your are referring to, in which case you will have to use if statements)

So I would suggest going with the 1st options unless renaming the label is not an option.

HTH

Best,

Sunny

Re: Using values of hidden column

Tue, 28 Apr 2015 12:31:34 GMT

I have a dimension named Area, and I should tell you guys that my columns who calculates Area only works when I have that dimension, I tried removing her but then all values about Area disappear.

What I need is to pass all values that are in the column Area (when she is not hidden) to a field and then calculate my avg(sales) only on these Areas, so I need to use her when she is hidden.

I tried rename like you said but still can't use her when she is hidden, I dont get any value as return.

Re: Using values of hidden column

Tue, 28 Apr 2015 12:47:14 GMT

I don't know if that matter, but this is my formula that I'm hiding or showing:

if(count(DISTINCT if(Area= Area2, Area) ) >0, Area)

I tried to hide other expressions and use them when they were hidden, and I manage to use them even hidden, but when I try to use the column with that formula above, simply isn't working.

Is there a problem with that formula?

Re: Using values of hidden column

sunny_talwar — Tue, 28 Apr 2015 12:53:41 GMT

What would be more useful is, if you can share your application after hiding/masking your key fields so that one of us can show you how exactly it can be done.

Best,

Sunny