Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
vamshi_1957
Creator II
Creator II

Where condition in resident load with "outer join"

Hi,

I have a question. Can we use,

Load field name, field name1, field name2 from TableA;

Load * from Table B;

left join

Load * from Table C;

Outer Join (Table A)

Load field name, field name1, field name2, field name3

Resident Table B

where not exists (field name);

Can we use the "where not exists" in resident load and join this table to already loaded table?

6 Replies
Not applicable

You can do that.

For you to do a outer join to B from C, please use the following format.

left join (B)

load * resident C;

And then when you want to outer join to A, use the following syntax

outer join(A)

load .......

resident B

where not exists ( <previously loaded field>, <field being loaded>);

Make sure <previously loaded field> and <field being loaded> are having different field names. Use aliasing.

Thanks,

Ram

jaimeaguilar
Partner
Partner

Hi,

I don't know what is your expected result, but I think that script will run without errors,

regards

vamshi_1957
Creator II
Creator II

Hi Ramkumar,

Thanks for your response. What if i have the same field name. Can i use (where not exists (common field name)?

Not applicable

Hi Vamshi, it is just for readability. You can still use the same name.

Not applicable

you can also specify one argument, where it implies the field name is common.

vamshi_1957
Creator II
Creator II

I have this script,

Table A:

Load Fieldname1, Fieldname2.......Fieldname48

from Table A;

Table B:

Load Fieldname1, Fieldname2.......FieldnameXX

From Table B;

Left join

Load Fieldname1, Fieldname2.......FieldnameYY

from Table C;

Outer join (Table A)

Load *

Resident Table B

where not exists (Fieldname1);

Store TableA into TableA.qvd (qvd);

So, Finally, there are 48 fields in table A and 52 fields in Table B (formed by joining Table B and Table C). These 48 fields are common in both tables. Say, for suppose, i have 7814 x 48 in Table A. I should get more number of rows after i run this script (because it will bring in the extra rows and 4 extra columns from table B).

But i am still getting 7814x48 and the TableA.qvd is not updating.

But when i use the following script, it is working.

Table A:

Load Fieldname1, Fieldname2.......Fieldname48

from Table A;

Table B:

Load Fieldname1, Fieldname2.......FieldnameXX

From Table B;

Left join

Load Fieldname1, Fieldname2.......FieldnameYY

from Table C;

Store TableB into TableB.qvd(qvd);

Drop TableB;

Outer join (Table A)

Load *

From Table B.qvd(qvd)

where not exists (Fieldname1);

Store TableA into TableA.qvd (qvd);

It is giving me 9509x52 in the final table and the TableA.qvd (qvd) is updating to the latest.

So, i want to know can we use the outer join to already loaded table using resident and where condition?