cartesian product type problem

[#1]

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

[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:

[#3]

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 · Accepted solution]

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

[#5]

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]

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;