Nested Dimension

[evansabres]

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.

[vishsaggi]

Can you share some sample data with expected output?

[Anonymous]

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]

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

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]

I was able to apply this to my problem