Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jim_chan
Specialist
Specialist

How to use where not exists.

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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

9 Replies
sunny_talwar

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

jim_chan
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Luminary Alumni
Luminary Alumni

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
Specialist
Specialist
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.