topic Re: cross join with condition in QlikView

cross join with condition

Sat, 18 Jun 2011 20:19:17 GMT

Hello All,

I have two table with following kind of structure

Table A:

Filed1

Table B:

Field 2

Now I want the two tables to join such that Table A fields are cross joined with Table B fields such that table B Field is greater than or equaly to Table 1 Fields

For example

New Table:

Field1 Field2

1 1

1 2

1 3

2 2

2 3

2 4

3               3
3               4
3               5
.               .
.
.
.
7               7
7               8
7               9
.
.
.
9               9

I think the syntax should be something like

New_Table:
Load Field1 
RESIDENT A;
JOIN (New_Table)
LOAD FIELD2
RESIDENT B
WHERE B>=A;

Can osmeone help please

-br

Arif

Re: cross join with condition

nagaiank — Sat, 18 Jun 2011 20:41:50 GMT

One way of getting the result you want is using the following script:

TempTable:

NoConcatenate LOAD Field1 Resident TableA;

Join (TempTable) LOAD Field2 Resident TableB;

NewTable:

NoConcatenate LOAD Field1, Field2 Resident TempTable Where Field1 >= Field2;

DROP Table TempTable;

Hope this helps.

Re: cross join with condition

Sat, 18 Jun 2011 22:31:44 GMT

Hello,

Thanks for the reply. Actually i would like to avoid the full outer join, because the tables i am using are very large (in millions of rows), and this could cause a very considerable memory problem. Is there any way that we could acheive the same result, without going to full outer join first and then selecting our desired rows. I mean in the same step when we are making the full cross or outer join, I would like to restrict it so that it does not go into a full outer join and the result is restricted to the condition that i give to it

Arif