Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jim_chan
Contributor III

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: How to use where not exists.

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.

9 Replies

Re: How to use where not exists.

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

jim_chan
Contributor III

Re: How to use where not exists.

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.

What is the script that you are using?

jim_chan
Contributor III

Re: How to use where not exists.

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.

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
Contributor III

Re: How to use where not exists.

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

MVP
MVP

Re: How to use where not exists.

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
Contributor III

Re: How to use where not exists.

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

MVP
MVP

Re: How to use where not exists.

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.

Community Browser