topic How to use where not exists. in QlikView

How to use where not exists.

jim_chan — Wed, 28 Sep 2016 02:29:46 GMT

Hi guys,

I have 2 years(2015 and 2016) data. i need to check which wan is a new IDs created in years 2016 compare to 2015.

so i load in 2015 data 1st.

then continue to 2016 data, and add with

where not exists(my_id); ?

Rgds,

Jim

Re: How to use where not exists.

sunny_talwar — Wed, 28 Sep 2016 02:35:38 GMT

Looks like a right approach, what issue are you running into?

Re: How to use where not exists.

jim_chan — Wed, 28 Sep 2016 02:39:49 GMT

Hey bro,

as i checked the IDs count in 2015 is 8000 , 2016 is 8099. So, i wanted to check the the new 99 IDs.

but now, even if i have out where not exists, it still still showing me total of 8099.

Jim

Re: How to use where not exists.

sunny_talwar — Wed, 28 Sep 2016 02:40:55 GMT

What is the script that you are using?

Re: How to use where not exists.

jim_chan — Wed, 28 Sep 2016 02:43:18 GMT

like this

2016:

LOAD my_id,

record_status

FROM

[..\sample_2016.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

2015:

LOAD my_id,

record_status

FROM

[..\sample_2015.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where not exists(my_id);

Re: How to use where not exists.

sunny_talwar — Wed, 28 Sep 2016 02:45:46 GMT

May be try this:

2015:

LOAD my_id,

record_status

FROM

[..\sample_2015.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

2016:

NoConcatenate

LOAD my_id,

record_status

FROM

[..\sample_2016.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where not exists(my_id);

DROP Table 2015;

Re: How to use where not exists.

jim_chan — Wed, 28 Sep 2016 02:51:48 GMT

Got it! this will let me know what is the new id.

but, how abotu keeping all records (total of 8099) and also able to have a record of new IDs(total of 93) ?

Rgds,

Jim

Re: How to use where not exists.

jagan — Wed, 28 Sep 2016 02:57:50 GMT

2015:

LOAD my_id,

record_status

FROM

[..\sample_2015.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

2016:

LOAD my_id,

record_status

FROM

[..\sample_2016.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where exists(my_id);

Concatenate(2016)

LOAD my_id,

record_status,

1 AS New_ID /// Field which holds new id flag

FROM

[..\sample_2016.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where not exists(my_id);

DROP TABLE 2015;

Hope this helps you

Regards,

Jagan.

Re: How to use where not exists.

jim_chan — Sat, 08 Oct 2016 15:54:30 GMT

when i use concat (2016) , it will say table not found. is it concatenate(2015) intsaed 2016?

Re: How to use where not exists.

jagan — Sun, 09 Oct 2016 03:58:14 GMT

Try this script. Added NoConcatenate for 2016 table.

2015:

LOAD my_id,

record_status

FROM

[..\sample_2015.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

2016:

NoConcatenate

LOAD my_id,

record_status

FROM

[..\sample_2016.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where exists(my_id);

Concatenate(2016)

LOAD my_id,

record_status,

1 AS New_ID /// Field which holds new id flag

FROM

[..\sample_2016.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where not exists(my_id);

DROP TABLE 2015;

Hope this helps you

Regards,

Jagan.