Where Not Exist - not working

[mcsshg2011]

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.

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

[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.

[sunny_talwar]

Did not see the second load ...

I like being explicit more often then not

[mcsshg2011]

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;