topic Re: Generate all possible combinations with result in QlikView

Generate all possible combinations with result

Anonymous — Thu, 16 Nov 2017 14:28:47 GMT

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

Re: Generate all possible combinations with result

sunny_talwar — Thu, 16 Nov 2017 16:54:58 GMT

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;

Re: Generate all possible combinations with result

Anonymous — Thu, 16 Nov 2017 17:32:35 GMT

Thank you Sunny. You are really awesome.

Re: Generate all possible combinations with result

ingo_lsg — Wed, 20 May 2020 13:40:34 GMT

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

LOAD Distinct col1...

LOAD Distinct col2..

LOAD Distinct col2...