topic AW:Cartesian product in QV in QlikView

Cartesian product in QV

theviking — Mon, 07 Dec 2009 20:11:20 GMT

Hi,

I would like to create the cartesian product by joining two tables. Table1 is read into QV from a database and the other is generated internally within QV. An example of what I would like to do is:

Table1

Field1 Field2 Field3

1 A 22

2 B 71

Table2

Field2

The "join" should result in the following table

Field1 Field2 Field3

1 A 22

1 B -

1 C -

2 A -

2 B 71

2 C -

Any suggestions on how this can be done?

Thanks,

/HJ

AW:Cartesian product in QV

Mon, 07 Dec 2009 21:08:04 GMT

Hi, you can use simple JOINs for it. But i think, this just works for small amount of data. For your example:


LOAD
 Field1
FROM
Table1;

OUTER JOIN

LOAD Field1 AS Field2
FROM
 Table2;

LEFT JOIN
LOAD
 Field1,
 Field2,
 Field3
FROM
 Table1;

Kind regards.

AW:Cartesian product in QV

theviking — Mon, 07 Dec 2009 21:40:46 GMT

Thanks!!!

It worked like a charm. The only modification I did was to add a distinct option to the first load statement.

LOAD DISTINCT Field1
FROM
Table1;

OUTER JOIN

LOAD Field1 AS Field2
FROM
Table2;

LEFT JOIN

LOAD
Field1,
Field2,
Field3
FROM
Table1;

Many thanks!

/HJ