Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

HowTo conditionally replace values in a pivot table

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?

example.PNG

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

8 Replies
simenkg
Specialist
Specialist

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.

Not applicable
Author

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.

consenit
Partner - Creator II
Partner - Creator II

Hi There.

See QVW attached.

Kind Regards,

Ernesto.

Not applicable
Author

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

consenit
Partner - Creator II
Partner - Creator II

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.

Anonymous
Not applicable
Author

it works with 11.20 SR 5!

Not applicable
Author

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

Anonymous
Not applicable
Author

hmm interesting...thanks!