Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mcsshg2011
New Contributor

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.

Tags (1)
10 Replies

Re: Where Not Exist - not working

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_;

Re: Where Not Exist - not working

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

Re: Where Not Exist - not working

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);

Re: Where Not Exist - not working

Smart to add Concatenate because AutoConcatenate won't work anymore

Re: Where Not Exist - not working

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;

Re: Where Not Exist - not working

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

Re: Where Not Exist - not working

Did not see the second load ...

I like being explicit more often then not

mcsshg2011
New Contributor

Re: Where Not Exist - not working

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_);

Re: Where Not Exist - not working

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;

Community Browser