topic Opposite Functionality of Join in QlikView

Opposite Functionality of Join

pavanthota — Fri, 11 May 2012 07:10:31 GMT

Hi,

Can any one assist for the below scenario

I have two tables like below

Table 1:

Load

from ;

Table2:

Load

a,e,f,

from ;

In final table i need to satisfy the below condition

Final Table:

Load

From

Where Table1.a <> Table2.a;

Opposite Functionality of Join

rbecher — Fri, 11 May 2012 07:50:03 GMT

Hi Pavan,

if I understand it right I think a join (or anti join) is not needed here because in you result you want to load only records from table1 if no corresponding record exist in table2.

Therefor you could do first load all distinct values of field a of table2 and then load table1 where not exists(a):

t1:

load distinct a from...;

final:

noconcatenate load a, b, c from ...

where not exists(a);

drop table t1;

- Ralf

Opposite Functionality of Join

pavanthota — Fri, 11 May 2012 10:44:13 GMT

Hi Ralf,

Thanks for your help

I am new to Qlikview can you give me the below one script in qlikvew approach

"t1:

load distinct a from...;

final:

noconcatenate load a, b, c from ...

where not exists(a);

drop table t1;"

Thanks,

Pavan

Opposite Functionality of Join

rbecher — Fri, 11 May 2012 11:09:46 GMT

Hi Pavan,

it is a QlikView script. Just add the FROM part and substitue the field names.

- Ralf

Opposite Functionality of Join

Fri, 11 May 2012 11:16:44 GMT

Hi Pavan,

there are several ways to do that. Here is example with joins (copy bolded code in Script editor and reload QV document; result is in FINAL table):

TABLE_1:

LOAD * INLINE [

A, B, C

1, b1, c1

2, b2, c2

3, b3, c3

4, b4, c4

5, b5, c5

];

TABLE_2:

LOAD * INLINE [

A, E, F

1, e1, f1

2, e7, f7

7, e2, f2

8, e3, f3

9, e4, f4

];

//get intersection of tables A field

TEMP_TABLE:

load A as A_TEMP Resident TABLE_1;

inner Join

LOAD A as A_TEMP Resident TABLE_2;

TEMP_TABLE_1:

LOAD A as A_TEMP, B as B_TEMP, C as C_TEMP Resident TABLE_1;

left join

load A_TEMP, A_TEMP as X Resident TEMP_TABLE;

drop Table TEMP_TABLE;

FINAL:

load A_TEMP AS A_FINAL, B_TEMP as B_FINAL, C_TEMP AS C_FINAL Resident TEMP_TABLE_1 where not exists(X);

drop Table TEMP_TABLE_1;

regards

Opposite Functionality of Join

rbecher — Fri, 11 May 2012 11:25:45 GMT

To make it more clearly:

temp :

load distinct a from Table2;

final:

noconcatenate load a, b, c from Table1

where not exists(a);

drop table temp.

Re: Opposite Functionality of Join

pavanthota — Fri, 11 May 2012 14:31:50 GMT

HI Krunoslav Pap

Thanks for your response

I have millions of data for that millions its giving wrong results, can u suggest me and make it as simple

Thanks,

Pavan

Re: Opposite Functionality of Join

pavanthota — Fri, 11 May 2012 14:46:41 GMT

Hi Ralf,

I tried based on your example i am attaching the example can u please guide me in proper direction

Thanks

Pavan

Re: Opposite Functionality of Join

pavanthota — Fri, 11 May 2012 14:48:32 GMT

Hi Ralf,

I tried based on your example i am attaching the example can u please guide me in proper direction

Thanks

Pavan

Re: Opposite Functionality of Join

rbecher — Sun, 13 May 2012 17:45:21 GMT

Hi Pavan,

this is my approach:

TABLE1:

LOAD * INLINE [

A, B, C

1, b1, c1

2, b2, c2

3, b3, c3

4, b4, c4

5, b5, c5

];

TABLE2:

LOAD * INLINE [

A, E, F

1, e1, f1

2, e7, f7

7, e2, f2

8, e3, f3

9, e4, f4

];

temp:

load distinct A as X

Resident TABLE2;

DROP Table TABLE2;

Final:

NoConcatenate Load A, B, C Resident TABLE1

where not exists(X, A);

DROP Tables temp, TABLE1;

//-------------

It's loading all records from Table1 where no corresponding record exists in Table2 (if field A is the key).

- Ralf