Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
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;