Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
educastri83
Creator
Creator

Pivot chart for control of duplicates

Hello everyone,

community I come to you to solve this:
I would like to create a pivot chart that shows the following dimensions:
- "Identity Number" that have more than one account (this happens through duplicate users with different account names)
- "Role" that appear more than once in the same "Identity Number"

As expression:
- To be able to count the amount of the total of roles repeated by "Identity Number"

In the example I leave a sample of how the desired pivot chart should be.example1.png

Although it is not a priority for now, there are some dirty values in the "Identity number" field, for example: the user with the identity number 33333333 is the same as the 3333333-3 and I would like to clean and validate that value to have accurate roles repeated by user.

Thank you!

Labels (4)
1 Solution

Accepted Solutions
PrashantSangle

use if condition.
try below
if(Count(Total<Account> Distinct Role)>1,Count(Total<Account> Distinct Role))

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

7 Replies
PrashantSangle

1: take
Dimension : Identity number, Account, Role
Measure : Count(Total<Account> Distinct Role)

2: use Replace function to remove "-"
Replace([Identity Number],'-','')

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
ramachandra_gee
Contributor III
Contributor III

Hi Look at the attached one and let me know if any queries.

 

 

Thanks,'

'Ram.

ramachandra_gee
Contributor III
Contributor III

Hi,

Expected output as in attachment please check and let me know.

 

Thanks,

Ram

educastri83
Creator
Creator
Author

Hi! thank you for your answer!

almost almost solved… The list of the pivot chart should not include cases that are not repeated more than once by identity number.

For example for the following cases it does not have a role that it appears more than once by Identity Number and they are some of those that should not be shown on the table:

 
 
 
educastri83
Creator
Creator
Author

Thanks Prashant!
The function replace it works
PrashantSangle

use if condition.
try below
if(Count(Total<Account> Distinct Role)>1,Count(Total<Account> Distinct Role))

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
educastri83
Creator
Creator
Author

very nearly @PrashantSangle ,

The result you are giving is if the account appears> 1 but the calculation that should be shown when a role appears> 1 in the same Identity number.

for example, in the case of identity number 12345678 it has that role> 1

example rev.png

Attach the qvw. file example of the value you gave me and a text object with some comments.

Thank you