Generate all possible combinations with result

[Anonymous]

HI all,

Please help me to get the following output from the given data table. If the combination is present in the data table then result is Yes otherwise No.

Data table:
col1 Col2 Col3
A     X    1
B     Y    2
C     Z    3
A     X    2
B     Y    3

Output:

Col1   Col2  Col3   Result
A       X     1       Yes
B       X     1       No
C       X     1       No
A       X     2       Yes
B       X     2       No
C       X     2       No
A       X     3       No
B       X     3       No
C       X     3       No
A       Y     1       No
B       Y     1       No
C       Y     1       No
A       Y     2       No
B       Y     2       Yes
C       Y     2       No
A       Y     3       No
B       Y     3       Yes
C       Y     3       No
A       Z     1       No
B       Z     1       No
C       Z     1       No
A       Z     2       No
B       Z     2       No
C       Z     2       No
A       Z     3       No
B       Z     3       No
C       Z     3       Yes

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD *,

col1&col2&col3 as Flag;

LOAD * INLINE [

    col1, col2, col3

    A, X, 1

    B, Y, 2

    C, Z, 3

    A, X, 2

    B, Y, 3

];

NewTable:

LOAD Distinct col1

Resident Table;

Join (NewTable)

LOAD Distinct col2

Resident Table;

Join (NewTable)

LOAD Distinct col3

Resident Table;

FinalTable:

LOAD col1,

col2,

col3,

If(Exists(Flag, col1&col2&col3), 'Yes', 'No') as Result

Resident NewTable;

DROP Tables Table, NewTable;

[Anonymous]

Thank you Sunny. You are really awesome.

[ingo_lsg]

@sunny_talwar 

HI,

could I somehow loop through the columns of the table and create a more dynamic script?

So that I don't have to put in the code for every column manually?

Like

LOAD Distinct col1...

LOAD Distinct col2..

LOAD Distinct col2...