Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

where not exists and only get one line

Hi I have incremental reload with where not exists syntax.

But I am getting only one record for the history.

I have been doing WHERE NOT EXISTS for many times, this is the first time I've seen this.

//today

LOAD Account,

          Blalance,

           PRCS_DTE

FROM

[..\03_QVD\Table_Incr.qvd]

(qvd);

//History

LOAD Account,

          Blalance,

           PRCS_DTE

FROM

[..\03_QVD\Table.qvd]

(qvd)

WHERE NOT EXISTS (PRCS_DTE)

;

where_exists.png

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: where not exists and only get one line

That's because the symbol table will be updated as soon as the first record with a new PRCS_DTE value is loaded in (i.e. it is updated during the LOAD, not at the end of the statement).

Hence WHERE NOT EXISTS returns false for any following record with the same value.

Try

LOAD Account,

          Blalance,

           PRCS_DTE,

           PRCS_DTE as PRCS_TODAY

FROM

[..\03_QVD\Table_Incr.qvd]

(qvd);

//History

LOAD Account,

          Blalance,

           PRCS_DTE

FROM

[..\03_QVD\Table.qvd]

(qvd)

WHERE NOT EXISTS (PRCS_TODAY, PRCS_DTE);

DROP FIELD PRCS_TODAY;

5 Replies
MVP
MVP

Re: where not exists and only get one line

That's because the symbol table will be updated as soon as the first record with a new PRCS_DTE value is loaded in (i.e. it is updated during the LOAD, not at the end of the statement).

Hence WHERE NOT EXISTS returns false for any following record with the same value.

Try

LOAD Account,

          Blalance,

           PRCS_DTE,

           PRCS_DTE as PRCS_TODAY

FROM

[..\03_QVD\Table_Incr.qvd]

(qvd);

//History

LOAD Account,

          Blalance,

           PRCS_DTE

FROM

[..\03_QVD\Table.qvd]

(qvd)

WHERE NOT EXISTS (PRCS_TODAY, PRCS_DTE);

DROP FIELD PRCS_TODAY;

Not applicable

Re: where not exists and only get one line

Thank you Stefan, what does "symbol table" mean?

MVP
MVP

Re: where not exists and only get one line

That's where the distinct field values are stored:

Symbol Tables and Bit-Stuffed Pointers

Not applicable

Re: where not exists and only get one line

Thank you very much! Tremendously helpful!

johngouws
Contributor II

Re: where not exists and only get one line

Hello Stefan,

I had a similar issue as Jessica today and I could adapt your solution to work for me. 

Thank you, very much appreciated.