Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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;