topic Show value if specific value in other column in QlikView

Show value if specific value in other column

Wed, 11 Sep 2013 07:45:30 GMT

Hey guys,

Small question concerning a pivot table.

I have got a pivot table with the following columns:

dimension "ordernumber" || dimension "article"

Basically, I need all the order numbers that contain the article "x".

So if the second column contains "x" it should show the ordernumber, AND all the other articles that were in this order.

If the order does not contain the value x, I do not want the see the order nor all its articles.

Why I am stuck, I cannot just use an expression with article = {'xtrans'} because it would then show me the article xtrans, but hide all the other articles in this order.. See my problem?

Thanks for the help!

Re: Show value if specific value in other column

tresB — Wed, 11 Sep 2013 07:51:56 GMT

try something like:

Only({ordernumber=p({1<article={'x'}>})}article)

Edit: included '1' to disregard selection

Re: Show value if specific value in other column

Wed, 11 Sep 2013 08:00:27 GMT

I do not understand the expression you have given me.

What does the "p" stand for? And the "1"?

Re: Show value if specific value in other column

tresB — Wed, 11 Sep 2013 08:13:10 GMT

These are set analysis elements. Goto help and search for set analysis.

Re: Show value if specific value in other column

Wed, 11 Sep 2013 08:18:17 GMT

Will do, but unfortunately it does not work..

It shows the p red underlinded..

Re: Show value if specific value in other column

Wed, 11 Sep 2013 08:33:39 GMT

Think we had forgotten the $ sign. Adding this to the function the underlined p is OK now.

Other question though, I am supposed to pased this in the calculated dimension of "ordernumber" right?

Or where should I use this?

Re: Re: Show value if specific value in other column

tresB — Wed, 11 Sep 2013 08:43:12 GMT

Sorry, corrected expression :Only({<ordernumber=p({1<article={'x'}>})>}article). Try to use this as expression. If does not work, please share a sample app.

Re: Show value if specific value in other column

Wed, 11 Sep 2013 08:49:28 GMT

The expression seems to be ok, but when I paste your function in the used dimension box, I get the "// error in calculated dimension"

I am now using

*the calculated dimension you told me, and

*the dimension "article"

Is that ok or am I using the function in the wrong place?

Thank you

Re: Re: Show value if specific value in other column

tresB — Wed, 11 Sep 2013 08:54:45 GMT

Request you to share the sample, that would minimize this loop, if you are worried about data security, please see this link

Re: Re: Re: Show value if specific value in other column

Wed, 11 Sep 2013 09:24:59 GMT

Here is a file with my dimensions..

The funny thing is, it does give me a value for sales, but it does not want to show me the order numbers

Re: Re: Re: Re: Show value if specific value in other column

tresB — Wed, 11 Sep 2013 09:29:32 GMT

Is this what you want? PFA.

Re: Show value if specific value in other column

Wed, 11 Sep 2013 09:35:50 GMT

Yes that's the one!!

So now it shows me 111 and 133 since they both contain the value x1. Awesome!

One last addition though.

If I add another dimension "description", how should I add this to this calculated dimension?

Re: Re: Re: Re: Show value if specific value in other column

tresB — Wed, 11 Sep 2013 09:40:07 GMT

If it's a description of the article, then simply add that dimension after article. If it is the description of orderno, then use :

Aggr(Only({<Ordernumber=p({1<Article={'x1'}>})>}Description),Description)

Re: Show value if specific value in other column

Wed, 11 Sep 2013 09:45:12 GMT

Great, thank you so much for your help and quick replies!!