Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

nenadvukovic
Contributor III

How to get records that exist in one table and not in the other?

Hi - this works fine resulting in the final table containing only rows with values 1 and 3:

B:

load * inline [

Field

2

4

5];

A:

noconcatenate load * inline [

Field

1

2

3]

where not exists (Field);     // in B

drop table B;

But, as I have several temporary tables in the for...next loop for other reasons this does not work good:

tmpB:

load * inline [

Field

2

4

5];

tmpA:

noconcatenate load * inline [

Field

1

2

3];

Final:

noconcatenate load * resident tmpA

where not exists (Field);     // in tmpB

drop table tmpA, tmpB;

Thanks

1 Solution

Accepted Solutions

Re: How to get records that exist in one table and not in the other?

Hi, only rename the field in tmpB, then in Final:

Final:

noconcatenate load * resident tmpA

       where not exists ('FieldB', Field)

11 Replies
hrlinder
Honored Contributor

Re: How to get records that exist in one table and not in the other?

give "Field" a different Name in tmpB:

tmpB:

load * Inline [

FieldB

2

4

5];

tmpA:

..

Final noconcatenate load * resident tmpA

where not exists (FieldB)

nenadvukovic
Contributor III

Re: How to get records that exist in one table and not in the other?

Then I get this

hrlinder
Honored Contributor

Re: How to get records that exist in one table and not in the other?

you Need to rename Field to FieldB in tmpA

tmpA:

load * inline [

FieldB,

1

2

3]

giakoum
Honored Contributor II

Re: How to get records that exist in one table and not in the other?

it does not work because all tables have the same field name so QV automatically concatenates them. you need to use noconcatenate

nenadvukovic
Contributor III

Re: How to get records that exist in one table and not in the other?

I end up with no records.

nenadvukovic
Contributor III

Re: How to get records that exist in one table and not in the other?

I did if you look into provided script

Re: How to get records that exist in one table and not in the other?

Hi, only rename the field in tmpB, then in Final:

Final:

noconcatenate load * resident tmpA

       where not exists ('FieldB', Field)

nenadvukovic
Contributor III

Re: How to get records that exist in one table and not in the other?

Basically I want a result that you get in MS SQL from this:

select Field from TableA

except

select Field from TableB

kimrormark
Contributor

Re: How to get records that exist in one table and not in the other?

tmpB:

load * inline [

FieldB

2

4

5];

tmpA:

noconcatenate load * inline [

Field

1

2

3];

Final:

noconcatenate load * resident tmpA

where not exists (FieldB, Field);     // in tmpB

drop table tmpA, tmpB;

I have renamed the Field in the tmpB to "FieldB". QlikView does not do any relations until the end of the script, and the field "Field" is just something that sort of "floats" around. So when you say "where not exists(Field)", it checks the values in both tables, not only in the tmpB-table.

Therefore, if you rename the field in one of the tables, you will be able to specify that specific field, and check it against the other field.

Hope this helps!