Opposite Functionality of Join

[pavanthota]

Hi,

Can any one assist for  the below scenario

I have two tables like below

Table 1:

Load

a,

b,

c

from     ;

Table2:

Load

a,e,f,

from   ;

In final table i need to satisfy the below condition

Final Table:

Load

a,

b,

c

From   

Where Table1.a <> Table2.a;

[rbecher]

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

[pavanthota]

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

[rbecher]

Hi Pavan,

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

- Ralf

[#5]

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

[rbecher]

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.

[pavanthota]

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

[pavanthota]

Hi Ralf,

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

Thanks

Pavan

[pavanthota]

Hi Ralf,

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

Thanks

Pavan

[rbecher · Accepted solution]

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