Expression Question

[evansabres]

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?

[timpoismans]

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]

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]

Yes, i want to count based on those RoleNames

[timpoismans]

Then try the following:

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

[evansabres]

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]

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]

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 · Accepted solution]

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.