topic Cartesian Join on table in QlikView

Cartesian Join on table

Anonymous — Sun, 28 May 2017 11:51:13 GMT

I have a table with 2 columns I want to do join on itself, and result table should contain multiple records for each combination of Table1.Column1 and Table1.Column2.

I am currently creating the table with default join, something like

Data:

Load Year, Month

from Table1;

join

Load Year as Year1,

Month as Month1

from Table1;

Table2:

Load

Year,

Month,

Year1,

Month1

Resident Data;

but since the record count of my table is 1M plus (I have many columns assigned for each 'Year' column), this is very timing consuming and script is failing due to huge data size. Below is my current table(Table1) and I want the final result table (Table2) to look like.

I guess dynamic for loop will take time and script will fail bcoz of huge data size. Any alternative ?

Table1

Year	Month
1996	71995
1997	81995
1998	91995

Final Result:

Table2

Year	Month
1996	71995
1996	81995
1996	91995
1997	71995
1997	81995
1997	91995
1998	71995
1998	81995
1998	91995

Thank you in advance.

Re: Cartesian Join on table

Anil_Babu_Samineni — Sun, 28 May 2017 13:15:10 GMT

You can split into two tables from single table like below

Approach 1

LOAD * Inline [

Year

1996

1997

1998

];

LOAD * Inline [

Month

71995

81995

91995

];

Approach 2:

Table1:

LOAD * Inline [

Year, Month

1996, 71995

1997, 81995

1998, 91995

];

NoConcatenate

LOAD Year as Year Resident Table1;

LOAD Month as Month Resident Table1;

DROP Table Table1;

Re: Cartesian Join on table

its_anandrjs — Sun, 28 May 2017 13:36:44 GMT

Try this ways because Join is good here.

SourceData:

LOAD * Inline

[

Year, Month

1996, 71995

1997, 81995

1998, 91995

];

NoConcatenate

LOAD Year Resident SourceData;

Join

LOAD Month Resident SourceData;

DROP Table SourceData;

Regards,

Anand