# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

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

Tags (2)
1 Solution

Accepted Solutions
MVP

## Re: cartesian product type problem

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

Table:

Col1

A

B

C

D

E

F

];

CountCol:

Resident Table;

LET vCount = Peek('CountCol');

DROP Table CountCol;

FOR i = 1 to \$(vCount)

Join(Table)

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
MVP

## Re: cartesian product type problem

Try this:

Table:

FROM

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

Join(Table)

FROM

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

Join(Table)

FROM

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

FinalTable:

NoConcatenate

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

Resident Table

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

DROP Table Table;

Output:

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?

Regards

V

MVP

## Re: cartesian product type problem

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

Table:

Col1

A

B

C

D

E

F

];

CountCol:

Resident Table;

LET vCount = Peek('CountCol');

DROP Table CountCol;

FOR i = 1 to \$(vCount)

Join(Table)

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.

MVP

## 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

## 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:
Col
A
B
C
D
E
F
G
H
];

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)
RESIDENT Origin;

CROSS:
CROSSTABLE (Col, Value)
RESIDENT Table;

TMP:
WHERE Max =1;
GROUP BY ID;
LOAD ID, Value, Count(Value) as Count
Resident CROSS
GROUP BY ID, Value;

TMPTABLE:
NOCONCATENATE
WHERE EXISTS(IDValid,recno());

DROP TABLE TMP, Table;

Rename Table TMPTABLE to Table;

NEXT

DROP TABLE Origin;

CROSS:
CROSSTABLE (Col, Value)