Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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

Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted

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

Highlighted

Smart to add Concatenate because AutoConcatenate won't work anymore

Highlighted

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;

Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted

Did not see the second load ...

I like being explicit more often then not

Highlighted
Contributor III
Contributor III

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

Highlighted

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;