Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
SB89
Contributor
Contributor

Hierarchical categorization of id by array entries of variable length

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 entryCorresponding array values(variable length per identifier)Desired categorization result after evaluating every array entry
1DA
1AA
2CB
2CB
2BB
3BA
3AA
3AA
3DA

 

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

1 Solution

Accepted Solutions
TimvB
Creator II
Creator II

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

View solution in original post

2 Replies
TimvB
Creator II
Creator II

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

View solution in original post

SB89
Contributor
Contributor
Author

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