Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
evansabres
Specialist
Specialist

Expression Question

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?

1 Solution

Accepted Solutions
timpoismans
Specialist
Specialist

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.

View solution in original post

10 Replies
timpoismans
Specialist
Specialist

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?

Anonymous
Not applicable

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?

marcus_sommer

Maybe:

COUNT(DISTINCT {<Flag={1}, RoleName = {'Season Ticket Gold',' Season Ticket Elite Gold', 'Platinum'}>} UserID)

- Marcus

evansabres
Specialist
Specialist
Author

Yes, i want to count based on those RoleNames

timpoismans
Specialist
Specialist

Then try the following:


Count(Distinct{<RoleName={'Season Ticket Gold',' Season Ticket Elite Gold', 'Platinum'}, Flag={1}>} UserID)

evansabres
Specialist
Specialist
Author

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'

timpoismans
Specialist
Specialist

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?

evansabres
Specialist
Specialist
Author

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.

timpoismans
Specialist
Specialist

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.