Hierarchical categorization of id by array entries of variable length

[SB89]

Hello Community,

I'm using QlikSense and I have a complex problem where I'm stuck and wondering how an expert would solve it.

Data structure:

Identifier of the DB entry	Corresponding array values(variable length per identifier)	Desired categorization result after evaluating every array entry
1	D	A
1	A	A
2	C	B
2	C	B
2	B	B
3	B	A
3	A	A
3	A	A
3	D	A

Every Id has an array, which contains a variable amount of entries that all need to be evaluated to reach the desired categorization in the third column. There are no rules whatsoever what might be inside this array, it might also only contain A's and have 1 to X entries. The categorization I need is a hierarchical one, meaning:

If at least one entry is A => the ID is Category A, Else if at least one entry is B => the ID is category B

and so forth....

As I'm no seasoned developer yet,  I would appreciate any pointers on how to approach such a task. At this point I don't even know how to do further research on this or how to describe the underlying problem. I already tried group by loads with match functions but they don't take into account that there are many entries to evaluate.

Do I need some kind of loop? Does QlikSense offer some kind of function for that?

Thank you in advance!

Cheers

Stan

[TimvB · Accepted solution]

Hi Stan,

You could use a mapping load * inline to load the hierarchical values to the categories of the second column. For example, A=1 and B=2. Then map the hierarchical values to each category (of the second column).

After that you could do a group by statement per ID to find the minimum hierarchical value per ID. Then you know for each ID the minimum hierarchical value, so you can use that information to map the original categories (which is the third column in your example).

Hope it helps. If you struggle with the scripting I can provide an example.

Tim

[SB89]

Hi Tim,

ha! I'm surprised how easy it is :D. Guess I was thinking too complicated.

I'm using Mapping Loads a lot so I know what to do 👍.

Thank you very much!

Cheers

Stan