Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
mcsshg2011
Contributor III
Contributor III

Where Not Exist - not working

Hi,

AccountHierarchy:

LOAD No_ as AccountNo

FROM $(pQVDExtractPath)AU_GLAccount.qvd (qvd) ;

LOAD No_ as AccountNo from $(pQVDExtractPath)TEC_AU_GLAccount.qvd (qvd) WHERE NOT Exists(No_);

In AU_GLAccount.qvd, there are 6 accounts starts with 413

In TEC_AU_GLAccount.qvd, there is an additional account 4131010.

If the script works, I expect to see 7 413 accounts. But, it does not work.

It only shows me what is in AU_GLAccount.qvd.

Help, please.

10 Replies
sunny_talwar

May be this

LOAD No_ as AccountNo,

     No_

FROM $(pQVDExtractPath)AU_GLAccount.qvd (qvd) ;

LOAD No_ as AccountNo

FROM $(pQVDExtractPath)TEC_AU_GLAccount.qvd (qvd)

WHERE NOT Exists(No_);

DROP Field No_;

Gysbert_Wassenaar

You rename No_ to AccountNo in the first load. That means that there is no No_ field with values that can be checked against when you start the second load.

Try this:

AccountHierarchy:

LOAD

     No_,

     No_ as AccountNo

FROM

     $(pQVDExtractPath)AU_GLAccount.qvd (qvd)

     ;

CONCATENATE (AccountHierarchy)

LOAD

     No_,

     No_ as AccountNo

FROM

      $(pQVDExtractPath)TEC_AU_GLAccount.qvd (qvd)

WHERE

     NOT Exists(No_)

;

DROP FIELD No_ FROM AccountHierarchy;


talk is cheap, supply exceeds demand
its_anandrjs

Try this way also for his you have to use proper field names

LOAD No_ as AccountNo_Not

FROM $(pQVDExtractPath)AU_GLAccount.qvd (qvd) ;

LOAD No_ as AccountNo from $(pQVDExtractPath)TEC_AU_GLAccount.qvd (qvd) WHERE NOT Exists(No_,AccountNo_Not);

sunny_talwar

Smart to add Concatenate because AutoConcatenate won't work anymore

its_anandrjs

T1:

LOAD No_ as AccountNo_Not

FROM $(pQVDExtractPath)AU_GLAccount.qvd (qvd) ;

T2:

LOAD No_ as AccountNo from $(pQVDExtractPath)TEC_AU_GLAccount.qvd (qvd) WHERE NOT Exists(No_,AccountNo_Not);

Drop Table T1;

Gysbert_Wassenaar

It will if you add No_ to both loads as I did, but it's often better to be explicit.


talk is cheap, supply exceeds demand
sunny_talwar

Did not see the second load ...

I like being explicit more often then not

mcsshg2011
Contributor III
Contributor III
Author

Hi,

Thank you all for responding.  

The final solution is :

AccountHierarchy:

LOAD No_ as AccountNo

FROM $(pQVDExtractPath)AU_GLAccount.qvd (qvd) ;

LOAD No_ as AccountNo from $(pQVDExtractPath)TEC_AU_GLAccount.qvd (qvd) WHERE NOT Exists(AccountNo,No_);

its_anandrjs

Your example is not working check

Assume this example

T1:

LOAD * Inline [

AccountNo_Not

A

B

C

D ];

T2:

LOAD * Inline [

AccountNo

A

B

C

D

e

f

g

h

] WHERE Not Exists(AccountNo_Not,AccountNo);

DROP Table T1;