Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Generate all possible combinations with result

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

1 Solution

Accepted Solutions
MVP

Try this

Table:

col1&col2&col3 as Flag;

col1, col2, col3

A, X, 1

B, Y, 2

C, Z, 3

A, X, 2

B, Y, 3

];

NewTable:

Resident Table;

Join (NewTable)

Resident Table;

Join (NewTable)

Resident Table;

FinalTable:

col2,

col3,

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

Resident NewTable;

DROP Tables Table, NewTable;

3 Replies
MVP

Try this

Table:

col1&col2&col3 as Flag;

col1, col2, col3

A, X, 1

B, Y, 2

C, Z, 3

A, X, 2

B, Y, 3

];

NewTable:

Resident Table;

Join (NewTable)

Resident Table;

Join (NewTable)

Resident Table;

FinalTable:

col2,

col3,

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

Resident NewTable;

DROP Tables Table, NewTable;

Anonymous
Not applicable
Author

Thank you Sunny. You are really awesome.

Contributor III

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