topic Re: Need to identify sets in QlikView

Need to identify sets

Wed, 25 Nov 2020 16:16:04 GMT

I have an interesting challenge that I'm sure is possible, but the solution is escaping me.

I have data in a table as such

Group Value

1 A

1 B

1 C

2 A

2 C

3 C

3 D

4 E

4 F

4 G

The result I need is as follows:

{A, B, C, D} and {E, F, G}

So, in this example I have two sets of Values/Letters where no letter overlaps from one set to the other.

Basically I need to all the different sets of letters that do not overlap any other sets.

I'd like for the solution to be done on the front end (not in the script) as the user may filter on the Values/Letters and get different results.

Hope this is makes sense, but will try and explain better if the question is not clear.

Thanks and appreciate any help!

Stan

Re: Need to identify sets

jonathandienst — Tue, 10 Jan 2017 13:36:25 GMT

i dont see how you get from the sample data to the desired result. How are the sets related to the fields?

Re: Need to identify sets

Tue, 10 Jan 2017 13:43:51 GMT

Hi Jonathan,

Groups 1 and 2 are related because they both have a Value of 'A' in common. Then groups 2 and 3 are related because they both have a Value of 'C'.

Since group 4 (the last group) does not have any Values in common with groups 1, 2, and 3, this makes our first set consist of groups 1, 2 and 3. The Values for 1, 2 and 3 are A, B, C, D.

Then group 4 will be a set by itself consisting of E, F, G.

Hope this helps?

Re: Need to identify sets

jonathandienst — Tue, 10 Jan 2017 13:57:27 GMT

I think I understand - and I don't think that this will be easy (or even possible?) in the front end and it is likely to perform badly if your data set is large. This is because there are several steps in the analysis.

It may be necessary to derive one or more flags in the load script and then you will have straightforward set expressions in the front end.

Re: Need to identify sets

MarcoWedel — Tue, 10 Jan 2017 23:55:56 GMT

Hi,

in case you might consider script solutions in the end, maybe this helps:

table1:

LOAD * INLINE [

Group, Value

1, A

1, B

1, C

2, A

2, C

3, C

3, D

4, E

4, F

4, G

];

Join LOAD Distinct

Value, Group as Group2

Resident table1;

Join LOAD Distinct

Group2, Value as Value2

Resident table1;

Join LOAD

Group,

Concat(DISTINCT Value2,', ') as Set,

AutoNumber(Concat(DISTINCT Value2,', ')) as SetNo

Resident table1

Group By Group;

tabResult:

LOAD Distinct

Group,

Value,

Set,

SetNo

Resident table1;

DROP Table table1;

regards

Marco

Re: Need to identify sets

MarcoWedel — Wed, 11 Jan 2017 00:25:44 GMT

Hi,

however, another more flexible solution (in terms of response to selections) might be:

table1:

LOAD * INLINE [

Group, Value

1, A

1, B

1, C

2, A

2, C

3, C

3, D

4, E

4, F

4, G

];

table2:

LOAD Distinct

Value, Group as Group2

Resident table1;

table3:

LOAD Distinct

Group2, Value as Value2

Resident table2;

hope this helps

regards

Marco

Re: Need to identify sets

daveamz — Wed, 11 Jan 2017 08:24:13 GMT

Check the attach. Is not a very elegant solution and definitely it will be very slow for large data sets.

Best,

David

Re: Need to identify sets

Wed, 11 Jan 2017 14:31:39 GMT

Hi Marco,

This is actually what I needed! Now in using this solution I've realized another scenario in the data, that I did not consider earlier.

In the resulting Sets I need to include, 2nd, 3rd, etc level connections. You know, like on LinkedIn, when you are connected to someone thru a mutual friend. Not directly connected.

So, in the screenshot below, Value A in Group 1 is connected to Value E in Group 3 and all is good. Now Value E in Group 3 is connected to Value F in Group 4. So, my resulting Set for A should also include the Value of F and so on.

In the screenshot the Desired Result column is what the final outcome needs to be.

Is this making sense?

In this example it is only going to 2nd level connections, but needs to go to 3rd, 4th, etc.

Appreciate your help!

Stan