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: 
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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

6 Replies
swuehl
MVP
MVP

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
Author

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

swuehl
MVP
MVP

That's where the distinct field values are stored:

Symbol Tables and Bit-Stuffed Pointers

Not applicable
Author

Thank you very much! Tremendously helpful!

johngouws
Partner - Specialist
Partner - Specialist

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.

marypapadopoulou
Contributor II
Contributor II

hi 

I created the new field but i still have the same problem.

I have a join statement in my first table, maybe this is the problem?