topic Hierarchical categorization of id by array entries of variable length in App Development
https://community.qlik.com/t5/App-Development/Hierarchical-categorization-of-id-by-array-entries-of-variable/m-p/1670145#M50633
<P>Hello Community,</P><P>I'm using QlikSense and I have a complex problem where I'm stuck and wondering how an expert would solve it.</P><P>Data structure:</P><TABLE border="1"><TBODY><TR><TD><STRONG>Identifier of the DB entry</STRONG></TD><TD><STRONG>Corresponding array values(variable length per identifier)</STRONG></TD><TD><STRONG>Desired categorization result after evaluating every array entry</STRONG></TD></TR><TR><TD>1</TD><TD>D</TD><TD>A</TD></TR><TR><TD>1</TD><TD>A</TD><TD>A</TD></TR><TR><TD>2</TD><TD>C</TD><TD>B</TD></TR><TR><TD>2</TD><TD>C</TD><TD>B</TD></TR><TR><TD>2</TD><TD>B</TD><TD>B</TD></TR><TR><TD>3</TD><TD>B</TD><TD>A</TD></TR><TR><TD>3</TD><TD>A</TD><TD>A</TD></TR><TR><TD>3</TD><TD>A</TD><TD>A</TD></TR><TR><TD>3</TD><TD>D</TD><TD>A</TD></TR></TBODY></TABLE><P> </P><P>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:</P><P>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</P><P>and so forth....</P><P> </P><P>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.</P><P>Do I need some kind of loop? Does QlikSense offer some kind of function for that?<BR /><BR />Thank you in advance!</P><P> </P><P>Cheers</P><P>Stan</P>Tue, 28 Jan 2020 16:18:09 GMTSB892020-01-28T16:18:09ZHierarchical categorization of id by array entries of variable length
https://community.qlik.com/t5/App-Development/Hierarchical-categorization-of-id-by-array-entries-of-variable/m-p/1670145#M50633
<P>Hello Community,</P><P>I'm using QlikSense and I have a complex problem where I'm stuck and wondering how an expert would solve it.</P><P>Data structure:</P><TABLE border="1"><TBODY><TR><TD><STRONG>Identifier of the DB entry</STRONG></TD><TD><STRONG>Corresponding array values(variable length per identifier)</STRONG></TD><TD><STRONG>Desired categorization result after evaluating every array entry</STRONG></TD></TR><TR><TD>1</TD><TD>D</TD><TD>A</TD></TR><TR><TD>1</TD><TD>A</TD><TD>A</TD></TR><TR><TD>2</TD><TD>C</TD><TD>B</TD></TR><TR><TD>2</TD><TD>C</TD><TD>B</TD></TR><TR><TD>2</TD><TD>B</TD><TD>B</TD></TR><TR><TD>3</TD><TD>B</TD><TD>A</TD></TR><TR><TD>3</TD><TD>A</TD><TD>A</TD></TR><TR><TD>3</TD><TD>A</TD><TD>A</TD></TR><TR><TD>3</TD><TD>D</TD><TD>A</TD></TR></TBODY></TABLE><P> </P><P>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:</P><P>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</P><P>and so forth....</P><P> </P><P>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.</P><P>Do I need some kind of loop? Does QlikSense offer some kind of function for that?<BR /><BR />Thank you in advance!</P><P> </P><P>Cheers</P><P>Stan</P>Tue, 28 Jan 2020 16:18:09 GMThttps://community.qlik.com/t5/App-Development/Hierarchical-categorization-of-id-by-array-entries-of-variable/m-p/1670145#M50633SB892020-01-28T16:18:09ZRe: Hierarchical categorization of id by array entries of variable length
https://community.qlik.com/t5/App-Development/Hierarchical-categorization-of-id-by-array-entries-of-variable/m-p/1670164#M50636
<P>Hi Stan,</P><P>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).<BR /><BR /></P><P>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).</P><P>Hope it helps. If you struggle with the scripting I can provide an example.<BR /><BR /></P><P>Tim</P>Tue, 28 Jan 2020 17:22:57 GMThttps://community.qlik.com/t5/App-Development/Hierarchical-categorization-of-id-by-array-entries-of-variable/m-p/1670164#M50636TimvB2020-01-28T17:22:57ZRe: Hierarchical categorization of id by array entries of variable length
https://community.qlik.com/t5/App-Development/Hierarchical-categorization-of-id-by-array-entries-of-variable/m-p/1670329#M50657
<P>Hi Tim,</P><P>ha! I'm surprised how easy it is :D. Guess I was thinking too complicated.<BR /><BR />I'm using Mapping Loads a lot so I know what to do <span class="lia-unicode-emoji" title=":thumbs_up:">👍</span>.</P><P>Thank you very much!</P><P> </P><P>Cheers</P><P>Stan</P>Wed, 29 Jan 2020 08:27:49 GMThttps://community.qlik.com/t5/App-Development/Hierarchical-categorization-of-id-by-array-entries-of-variable/m-p/1670329#M50657SB892020-01-29T08:27:49Z