Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

cartesian product type problem

Hi Qlik Community,

I have a table of values like below A, B, C

Column
A
B
C

I need to be able to create the following table that takes the variations of values from 1st table and concatenates them in 1 row.

New Column

A-B-C

A-C-B
B-A-C
B-C-A
C-A-B
C-B-A

I hope this made sense. I presume this would require some type of cartesian join, but i could be wrong. Please advise.

Regards

V

1 Solution

Accepted Solutions
sunny_talwar

Columns can be created, like this, trying to move forward from here:

Table:

LOAD * Inline [

Col1

A

B

C

D

E

F

];

CountCol:

LOAD Count(Col1) as CountCol

Resident Table;

LET vCount = Peek('CountCol');

DROP Table CountCol;

FOR i = 1 to $(vCount)

  Join(Table)

  LOAD Col as Col$(i)

  Inline [

  Col

  A

  B

  C

  D

  E

  F

  ];

NEXT

Feel free to give ideas or make changes to move forward, while I troubleshoot

View solution in original post

6 Replies
sunny_talwar

Try this:

Table:

LOAD Column as Col1

FROM

[https://community.qlik.com/thread/173474]

(html, codepage is 1252, embedded labels, table is @1);

Join(Table)

LOAD Column as Col2

FROM

[https://community.qlik.com/thread/173474]

(html, codepage is 1252, embedded labels, table is @1);

Join(Table)

LOAD Column as Col3

FROM

[https://community.qlik.com/thread/173474]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

NoConcatenate

LOAD *,

  Col1 & '-' & Col2 & '-' & Col3 as NewColumn

Resident Table

Where (Col1 <> Col2) and (Col1 <> Col3) and (Col2 <> Col3);

DROP Table Table;

Output:

Capture.PNG

Not applicable
Author

Thanks Sunny. I figured the solution would look something like yours. However, we are told that the 1st table has 3 values. What if we aren't told that ? How can we dynamically recode the logic and arrive at the same solution?

Thanks in advance.

Regards

V

sunny_talwar

Columns can be created, like this, trying to move forward from here:

Table:

LOAD * Inline [

Col1

A

B

C

D

E

F

];

CountCol:

LOAD Count(Col1) as CountCol

Resident Table;

LET vCount = Peek('CountCol');

DROP Table CountCol;

FOR i = 1 to $(vCount)

  Join(Table)

  LOAD Col as Col$(i)

  Inline [

  Col

  A

  B

  C

  D

  E

  F

  ];

NEXT

Feel free to give ideas or make changes to move forward, while I troubleshoot

Not applicable
Author

That helped sunny. Thanks.

sunny_talwar

That helped already??? Didn't we have to concatenate them?

I was spending time for no reason I guess

Well, I am glad you got what you were looking for.

Best,

Sunny

swuehl
MVP
MVP

Too bad QV only support equi-joins.

I started with a joining the original table in a loop, but without removing rows with duplicate column values, the script for permutations of 8 values run approx. 1 hour and started swapping.

This version filters rows after each join, so it runs only 1 minute 😉

Origin:

LOAD * Inline [

Col

A

B

C

D

E

F

G

H

];

CountCol:

LOAD Count(Col) as CountCol

Resident Origin;

LET vCount = Peek('CountCol');

DROP Table CountCol;

FOR i = 1 to $(vCount)

  If i = 1 Then

  Set vVar = 'Table:';

  ELSE

  Set vVar = 'Join (Table)';

  ENDIF

  $(vVar)

  LOAD Col as $(i)

  RESIDENT Origin;

  

  CROSS:

  CROSSTABLE (Col, Value)

  LOAD recno() as ID, *

  RESIDENT Table;

  TMP:

  LOAD ID as IDValid

  WHERE Max =1;

  LOAD ID,max(Count) as Max

  GROUP BY ID;

  LOAD ID, Value, Count(Value) as Count

  Resident CROSS

  GROUP BY ID, Value;

  TMPTABLE:

  NOCONCATENATE

  LOAD * Resident Table

  WHERE EXISTS(IDValid,recno()); 

  

  DROP TABLE TMP, Table;

  Rename Table TMPTABLE to Table; 

 

NEXT

DROP TABLE Origin;

CROSS:

CROSSTABLE (Col, Value)

LOAD recno() as ID, *

RESIDENT Table;

RESULT:

LOAD CONCAT(Value, '-', num#(Col) ) as Permut

Resident CROSS

GROUP BY ID;