Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: cartesian product type problem

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

6 Replies

Re: cartesian product type problem

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

Re: cartesian product type problem

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

Re: cartesian product type problem

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

Re: cartesian product type problem

That helped sunny. Thanks.

Re: cartesian product type problem

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

MVP
MVP

Re: cartesian product type problem

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;

Community Browser