topic Re: Need all possible combinations in output in QlikView

Need all possible combinations in output

Wed, 15 Oct 2014 11:54:13 GMT

Hi,

my input table is like below.

A B C

1 2 3

4 5 6

i need output as

A B C

1 2 3

2 1 3

3 1 2

4 4 6

5 4 6

6 4 5

Please suggest me the logic...

Re: Need all possible combinations in output

Wed, 15 Oct 2014 11:58:22 GMT

use join between the two table:

Load * From A;

Join

Load * from B;

Re: Need all possible combinations in output

Wed, 15 Oct 2014 12:06:39 GMT

Hi Rahul,

first table is from my source(input table),

i need to change it with all combinations and store it in qlikview as second table,

so that when we fetch one column data remaining column will have reference.

Re: Need all possible combinations in output

maxgro — Wed, 15 Oct 2014 12:10:30 GMT

maybe

tmp: // A B C

load * inline [

A, B, C

1, 2, 3

4, 5, 6

];

Concatenate (tmp) load A, B as C, C as B Resident tmp; // A C B

Concatenate (tmp) load A as B, B as A, C Resident tmp; // B A C

Concatenate (tmp) load A as C, B as A, C as B Resident tmp; // B C A

Concatenate (tmp) load A as C, B, C as A Resident tmp; // C B A

Concatenate (tmp) load A as B, B as C, C as A Resident tmp; // C A B

Re: Need all possible combinations in output

Wed, 15 Oct 2014 12:11:55 GMT

You can try this, but may be there is a better way:

LOAD * INLINE [
    A, B, C
    1, 2, 3
    4, 5, 6
];

NoConcatenate
NewRows:
LOAD * INLINE [
    A, B, C
];

For i = 0 to (NoOfRows('Input')-1)
      Let vA = Peek('A', $(i), 'Input');
      Let vB = Peek('B', $(i), 'Input');
      Let vC = Peek('C', $(i), 'Input');

     Concatenate(NewRows)
      LOAD * INLINE [
       A, B, C
       $(vA),$(vC),$(vB)
       $(vB),$(vA),$(vC)
       $(vB),$(vC),$(vA)
       $(vC),$(vA),$(vB)
       $(vC),$(vB),$(vA)
      ];

     next

Concatenate(Input)
Load *
Resident NewRows;

drop table NewRows;

Regards,

Angel

Re: Need all possible combinations in output

anbu1984 — Wed, 15 Oct 2014 12:15:42 GMT

Load * Inline [

A,B,C

1,2,3

4,5,6

];

NoConcatenate

Load B As A, A As B, C as C Resident A;

NoConcatenate

Load B As A, C As B, A as C Resident A;

NoConcatenate

😧

Load A As A, C As B, B as C Resident A;

NoConcatenate

Final:

Load * Resident A;

Concatenate

Load * Resident B;

Concatenate

Load * Resident C;

Concatenate

Load * Resident D;

Drop Tables A,B,C,D

Re: Need all possible combinations in output

Wed, 15 Oct 2014 12:27:34 GMT

Hi Massimo,

Thank you so much its working i got all the combinations....