Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Champion III
Champion III

Re: Nested Dimension

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
Highlighted
Champion III
Champion III

Re: Nested Dimension

Can you share some sample data with expected output?

Highlighted
Partner
Partner

Re: Nested Dimension

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)

Highlighted
Specialist
Specialist

Re: Nested Dimension

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?

Highlighted
Champion III
Champion III

Re: Nested Dimension

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

Highlighted
Specialist
Specialist

Re: Nested Dimension

I was able to apply this to my problem