Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
nenadvukovic
Creator III
Creator 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
rubenmarin

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

Final:

noconcatenate load * resident tmpA

       where not exists ('FieldB', Field)

View solution in original post

11 Replies
Anonymous
Not applicable

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
Creator III
Creator III
Author

Then I get this

Anonymous
Not applicable

you Need to rename Field to FieldB in tmpA

tmpA:

load * inline [

FieldB,

1

2

3]

giakoum
Partner
Partner

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

nenadvukovic
Creator III
Creator III
Author

I end up with no records.

nenadvukovic
Creator III
Creator III
Author

I did if you look into provided script

rubenmarin

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

Final:

noconcatenate load * resident tmpA

       where not exists ('FieldB', Field)

nenadvukovic
Creator III
Creator III
Author

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

select Field from TableA

except

select Field from TableB

kimrormark
Creator
Creator

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!