How to use where not exists.

[jim_chan]

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

[sunny_talwar]

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

[jim_chan]

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

[sunny_talwar]

What is the script that you are using?

[jim_chan]

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);

[sunny_talwar]

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;

[jim_chan]

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

[jagan]

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.

[jim_chan]

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

[jagan · Accepted solution]

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.