topic Re: Nested Dimension in QlikView

Nested Dimension

evansabres — Tue, 18 Sep 2018 17:45:56 GMT

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.

Re: Nested Dimension

vishsaggi — Tue, 18 Sep 2018 21:52:01 GMT

Can you share some sample data with expected output?

Re: Nested Dimension

Anonymous — Wed, 19 Sep 2018 06:18:03 GMT

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)

Re: Nested Dimension

evansabres — Wed, 19 Sep 2018 14:05:27 GMT

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?

Re: Nested Dimension

vishsaggi — Wed, 19 Sep 2018 14:20:52 GMT

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;

Re: Nested Dimension

evansabres — Wed, 19 Sep 2018 15:49:02 GMT

I was able to apply this to my problem