Need to identify sets

[#1]

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

[jonathandienst]

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

[#3]

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?

[jonathandienst]

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.

[MarcoWedel · Accepted solution]

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

[MarcoWedel]

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

[daveamz]

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

Best,

David

[#8]

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