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: 
evansabres
Specialist
Specialist

Nested Dimension

Not really sure how to categorize this question.

I have a field called 'UserID' and another field called 'RoleName'

Each UserID can have up to eight different values for 'RoleName;

For example, UserID 123456 can have a RoleName of Platinum, Elite Gold, Elite Red, Gold, Red, Silver, Bronze and Blue. However, Platinum is the highest in terms of ranking of these.

My end goal is to be able to have a chart or similar that shows an accurate number of UserID's in each RoleName. UserID 123456 is Platinum, but by virtue of the data, the UserID may show up in any of the seven other roles, but I only want it to be counted for Platinum, which is the highest.

'

Ideally, I would like to do this in the script. Looking for suggestions. Thank you.

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

May be you can try using mapping load like

MapLoad:

Mapping LOAD * INLINE [

RoleName, Rank

Platinum,      1

Elite Gold,     2

Elite Red,      3

Gold,             4

Red,              5

Silver,            6

Bronze,         7

Blue,             8

];



YourmainTable:

LOAD *,

          ApplyMap('MapLoad', RoleName) AS RoleRank

FROM yoursourceTablename;

View solution in original post

5 Replies
vishsaggi
Champion III
Champion III

Can you share some sample data with expected output?

Anonymous
Not applicable

to give you an idea (you must adapt to your script)

Fact:
load * Inline [
USERID, Role
123456, Platinum
123456, Elite Gold
234567, Gold
]
;

left join (Fact) load * Inline [
Role, Weight
Platinum, 1
Elite Gold,2
Gold, 3
]
;

Groupby:
load USERID,
min(Weight) as Weight
Resident Fact
group by USERID;

left join(Groupby)
load USERID, Role, Weight
resident Fact;

drop table Fact;

Table (you may use a Chart for your purpose)

evansabres
Specialist
Specialist
Author

So the data comes from a .tsv file that has over 89000 unique User_ID's.

How would this be done in a load script as opposed to an in-line table?

vishsaggi
Champion III
Champion III

May be you can try using mapping load like

MapLoad:

Mapping LOAD * INLINE [

RoleName, Rank

Platinum,      1

Elite Gold,     2

Elite Red,      3

Gold,             4

Red,              5

Silver,            6

Bronze,         7

Blue,             8

];



YourmainTable:

LOAD *,

          ApplyMap('MapLoad', RoleName) AS RoleRank

FROM yoursourceTablename;

evansabres
Specialist
Specialist
Author

I was able to apply this to my problem