topic Re: HowTo conditionally replace values in a pivot table in QlikView

HowTo conditionally replace values in a pivot table

Wed, 10 Dec 2014 15:29:30 GMT

I have a Pivot table with two dimensions (Proj, Name) and one expression (Hours).

In the case where there is only one name associated with the project, I need to replace the name with the tag "Confidential". Where there are more than one name, all names should be displayed.

I replaced the "Name" dimension with =If(count(Name)=1,'Confidential',Name). While the edit expression claimed it was OK, the table said "// Error in calculated dimension".

See below an example of what I trying to accomplish.

Any thoughts?

Re: HowTo conditionally replace values in a pivot table

simenkg — Wed, 10 Dec 2014 15:36:52 GMT

Although I am not a fan of calculated dimensions you can try:

Aggr(If(count(Name)=1,'Confidential',Name),Proj)

Remember to supress null values.

I would rather do this in the script by changing the key for a project if it only has one name associated with it. Then I would create a single user in the Name Dimension with the value 'Confidential'.

Then you can just use Proj and Name as dimensions and people will not be able to extract the information from the model.

By using a calculated dimension it will be possible to select the project and see the name as the only possible value in a Name listbox.

Re: HowTo conditionally replace values in a pivot table

Wed, 10 Dec 2014 15:45:26 GMT

Simen,

That sort of worked. It showed "Confidential" where I wanted it, --but-- it collapsed (aggregated) all of the multiple names / project into one line with a "-".

I agree with your concerns, but I was hoping to fix this in the table without touching the data structures.

Re: HowTo conditionally replace values in a pivot table

consenit — Wed, 10 Dec 2014 16:07:41 GMT

Hi There.

See QVW attached.

Kind Regards,

Ernesto.

Re: HowTo conditionally replace values in a pivot table

Wed, 10 Dec 2014 16:39:09 GMT

Ernesto,

That expression works in the app you provided, but not in my app, where it gives the same results as =Name.

I'm still investigating.

I'm using 11.2 SR4 - what version are you using?

John

Re: HowTo conditionally replace values in a pivot table

consenit — Wed, 10 Dec 2014 16:50:39 GMT

Strange I'm using a very old version indeed 11.00 SR 1 in my workplace, gotta try this later at home with 11.20 SR 8

Kind Regards,

Ernesto.

Re: HowTo conditionally replace values in a pivot table

Anonymous — Wed, 10 Dec 2014 17:16:00 GMT

it works with 11.20 SR 5!

Re: HowTo conditionally replace values in a pivot table

Wed, 10 Dec 2014 21:48:41 GMT

It works when I add in the "NODISTINCT" to the Aggr function.

=AGGR(IF(ONLY(AGGR( NODISTINCT COUNT(DISTINCT Name), Project))=1, 'Confidential', Name), Project, Name)

JLR

Re: HowTo conditionally replace values in a pivot table

Anonymous — Wed, 10 Dec 2014 22:02:00 GMT

hmm interesting...thanks!