Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
rohanwilliams
Contributor II
Contributor II

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
sunny_talwar

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;

View solution in original post

3 Replies
sunny_talwar

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;

rohanwilliams
Contributor II
Contributor II
Author

Thank you Sunny. You are really awesome.

ingo_lsg
Contributor III
Contributor III

@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...