topic Re: How to hide some data in Pivot table in QlikView

How to hide some data in Pivot table

Fri, 06 Sep 2013 06:02:09 GMT

Hi All,

I have the following table:

Name, Revenue

A,100

A(1),100

B,200

B(1),200

C,300

As you can see the some Name has relationship, like A and A(1) and they shared the same revenue.

What I want to is if the name has relationship, then show the Revenue in the second one, if not show it directly.

For the above sample, the result is in Pivot table and Use Name as dimension

Name, Revenue

A, -

A(1), 100

B,-

B(1),-

C,300

How to achieve it?

Thanks.

Re: How to hide some data in Pivot table

Fri, 06 Sep 2013 06:34:30 GMT

Hi,

You could try with:

if(Only(Name)<>SubField(Below(Only(Name)), '(', 1), sum(Revenue))

PFA

Re: How to hide some data in Pivot table

adnan_rafiq — Fri, 06 Sep 2013 07:52:39 GMT

Hi Isaac,

if you are not using duplicate rows any where else you can just exclude them when you reload data.

by using order by and left function like or you can use distinct function with left function

Load distinct left( Name,index(Name,'(')) as Name ,

Revenue

From file

if name has

Re: Re: How to hide some data in Pivot table

Thu, 26 Sep 2013 03:14:19 GMT

Hi Iosu, thanks for your expression, it works well. However, if I add one more dimension into the pivot table,

the Below function will find the wrong Name, how to solve it? And Because I change the Pivot table into Straight table. And I attached the QVW.

Re: Re: Re: How to hide some data in Pivot table

tresB — Thu, 26 Sep 2013 05:09:53 GMT

Use total keyword like attached sample.

Re: Re: How to hide some data in Pivot table

Thu, 26 Sep 2013 06:28:03 GMT

Hi Isaac,

Just add the TOTAL clause to the 'below' expression:

if(Only(Name)<>SubField(Below(TOTAL Only(Name)), '(', 1), sum(Revenue))

It works this way

Re: How to hide some data in Pivot table

Thu, 26 Sep 2013 08:53:01 GMT

Yes... it works very well, could you tell me, what's the meaning of that total keyword in this expression?

Re: Re: How to hide some data in Pivot table

tresB — Thu, 26 Sep 2013 12:44:04 GMT

TOTAL is used to disregard dimension. For better understanding, please go through the attached qvw.