Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an expression:
=COUNT(DISTINCT {<Flag={1}>} UserID)
I want to add a condition into this expression:
IF(RoleName = 'Season Ticket Gold',' Season Ticket Elite Gold', 'Platinum')
How would I structure this?
First off, as you have two overlapping RoleNames (Platinum) in each group, you might have UserID's that belong to both groups.
If the Flag=1 is still a requirement, try the following:
Group 1: Count(Distinct{<RoleName={'Season Ticket Red',' Season Ticket Elite Red', 'Platinum'}, Flag={1}>} UserID)
Group 2: Count(Distinct{<RoleName={'Season Ticket Gold',' Season Ticket Elite Gold', 'Platinum'}, Flag={1}>} UserID)
So in Group 1 you will have all distinct userID which have Red, Elite Red or Platinum as the RoleName
And Group 2 will have all distinct userID which have Gold, Elite Gold or Platinum as the RoleName.
As mentioned at the beginning, the sum of these two counts should be more than the total UserID's you have as UserID's that have the Platinum RoleName will be counted in both measures.
What's the purpose of the If-condition?
I mean, as it stands now, it's just outputting text based on the RoleName. It has no connection with your expression.
Or do you mean the following:
If RoleName=Season Ticket Gold OR Season Ticket Elite Gold OR Platinum, then count the UserID?
what is your Goal?
a Chart with a calulated Dimension IF(RoleName = 'Season Ticket Gold',' Season Ticket Elite Gold', 'Platinum') and your expr4ession above would give you the Count for both roles
else
what do you want to Combine? what is your expected Output?
Maybe:
COUNT(DISTINCT {<Flag={1}, RoleName = {'Season Ticket Gold',' Season Ticket Elite Gold', 'Platinum'}>} UserID)
- Marcus
Yes, i want to count based on those RoleNames
Then try the following:
Count(Distinct{<RoleName={'Season Ticket Gold',' Season Ticket Elite Gold', 'Platinum'}, Flag={1}>} UserID)
I apologize, I am doing a very poor job of explaining this. I also need to sum the count of Season Ticket Gold',' Season Ticket Elite Gold', 'Platinum'
It might just be me, but what do you want exactly?
To count the UserID where the RoleName is 'Season Ticket Gold',' Season Ticket Elite Gold', 'Platinum'?
Count how many times 'Season Ticket Gold',' Season Ticket Elite Gold', 'Platinum' appears in your data?
Again, I am doing a bad job of explaining. This data set has roughly 89000 unique UserID's, however each can have multiple RoleNames. In the script I did the following (based on the suggestion of another Qlik community member)
MapLoad:
Mapping LOAD * INLINE
[RoleName, Rank
Platinum, 8
Season Ticket Elite Gold, 7
Season Ticket Elite Red, 6
Season Ticket Gold, 5
Season Ticket Red, 4
Silver, 3
Bronze, 2
Blue, 1
];
TBL_USERROLES:
LOAD UserRoleID,
RoleGroupID,
RoleGroupName,
RoleID,
RoleName,
ApplyMap('MapLoad', RoleName) AS RoleRank,
UserID,
ExpiryDate,
EffectiveDate
FROM
[User_Roles.tsv]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq) WHERE(RoleName <> 'Season Ticket Holders') and (RoleName <> 'Top 138') and (RoleName <> 'Diamond') and (RoleName <> 'Gold') and (RoleName <> 'Employees');
Rank_Map:
Mapping
LOAD UserID &'|'& Max_Value as Key,
1 as Flag;
LOAD
UserID,
Max(RoleRank) as Max_Value
Resident TBL_USERROLES
Group By UserID;
Final:
NoConcatenate
LOAD
*,
ApplyMap('Rank_Map',UserID &'|'& RoleRank, 0) as Flag
Resident TBL_USERROLES;
DROP Table TBL_USERROLES;
So I have already accomplished counting the number of times these RoleNames appear. However, I was posed a different question. In this data set, a UserId that has a RoleName of Season Ticket Red, Season Ticket Elite Red or Platinum can be considered to be a member of Group 1. A User ID that has a role name of Season Ticket Gold, Season Ticket Elite Gold or Platinum can be considered to be a member of Group 2. So, I was asked how many Users are in each group. This isn't defined anywhere in my script or data set, which is why I was trying to do this in an expression.
First off, as you have two overlapping RoleNames (Platinum) in each group, you might have UserID's that belong to both groups.
If the Flag=1 is still a requirement, try the following:
Group 1: Count(Distinct{<RoleName={'Season Ticket Red',' Season Ticket Elite Red', 'Platinum'}, Flag={1}>} UserID)
Group 2: Count(Distinct{<RoleName={'Season Ticket Gold',' Season Ticket Elite Gold', 'Platinum'}, Flag={1}>} UserID)
So in Group 1 you will have all distinct userID which have Red, Elite Red or Platinum as the RoleName
And Group 2 will have all distinct userID which have Gold, Elite Gold or Platinum as the RoleName.
As mentioned at the beginning, the sum of these two counts should be more than the total UserID's you have as UserID's that have the Platinum RoleName will be counted in both measures.