Where not exists

[#1]

Good morning!  Would like some assistance in checking why script below is not working.  It is not returning the accounts missing from the.

GSR file...

///$tab Data

Activations:

LOAD [ACCOUNT NAME] as AcctName, 

     [COMing DATE] as ActivationDate,

     [PRODUCT GROUP],

     [CIRCUIT ID] as ServiceID,

     [ACCOUNT #] as Acct#,

     MRC,

     OTC   

FROM

(ooxml, embedded labels, table is incremental_data)

WHERE(TRANSACTION like 'ACTIVATION')

and [COMing DATE] >= '8/1/2016';

///$tab Fixed

LOAD [APPLICANTS NAME] as AcctName,

     [PRODUCT GROUP],

     [ACCT NO] as Acct#,

     [TEL NO] as ServiceID,   

     [MRC PHP] as MRC,

     [OTC PHP] as OTC,

     [DSR DATE] as ActivationDate  

FROM

(ooxml, embedded labels, table is incremental_fixed)

WHERE(TRANSACTION like 'ACTIVATION')

and [DSR DATE] >= '8/1/2016';

///$tab GSR

GSR:

LOAD [FA ID/Account Number] as Acct#,

     [Account Name],

     [Company Code],

     [Service Type],

     [Line Type],

     [Line Type Cascade],

     [Total August],

FROM

(ooxml, embedded labels, table is _xlnm._FilterDatabase);

///$tab Missing

Missing:

Load

Acct#,

AcctName,

ServiceID,

[PRODUCT GROUP],

MRC,

OTC,

ActivationDate

Resident Activations

where not Exists(Acct#);

Drop Table GSR;

[sunny_talwar]

May be try this:

///$tab Data

Activations:

LOAD [ACCOUNT NAME] as AcctName,

    [COMing DATE] as ActivationDate,

    [PRODUCT GROUP],

    [CIRCUIT ID] as ServiceID,

    [ACCOUNT #] as Acct#,

    MRC,

    OTC 

FROM

(ooxml, embedded labels, table is incremental_data)

WHERE(TRANSACTION like 'ACTIVATION')

and [COMing DATE] >= '8/1/2016';

///$tab Fixed

LOAD [APPLICANTS NAME] as AcctName,

    [PRODUCT GROUP],

    [ACCT NO] as Acct#,

    [TEL NO] as ServiceID, 

    [MRC PHP] as MRC,

    [OTC PHP] as OTC,

    [DSR DATE] as ActivationDate 

FROM

(ooxml, embedded labels, table is incremental_fixed)

WHERE(TRANSACTION like 'ACTIVATION')

and [DSR DATE] >= '8/1/2016';

///$tab GSR

GSR:

LOAD [FA ID/Account Number],

    [Account Name],

    [Company Code],

    [Service Type],

    [Line Type],

    [Line Type Cascade],

    [Total August],

FROM

(ooxml, embedded labels, table is _xlnm._FilterDatabase);

///$tab Missing

Missing:

Load

Acct#,

AcctName,

ServiceID,

[PRODUCT GROUP],

MRC,

OTC,

ActivationDate

Resident Activations

Where not Exists([FA ID/Account Number], Acct#);

DROP Table GSR;

[#3]

Thanks for the reply Sunny, tried what you suggested, and the the output is still not returning the missing accounts only.

[sunny_talwar]

May be try this:

///$tab Data

Activations:

LOAD [ACCOUNT NAME] as AcctName,

    [COMing DATE] as ActivationDate,

    [PRODUCT GROUP],

    [CIRCUIT ID] as ServiceID,

    [ACCOUNT #] as Acct#,

    MRC,

    OTC

FROM

(ooxml, embedded labels, table is incremental_data)

WHERE(TRANSACTION like 'ACTIVATION')

and [COMing DATE] >= '8/1/2016';

///$tab Fixed

LOAD [APPLICANTS NAME] as AcctName,

    [PRODUCT GROUP],

    [ACCT NO] as Acct#,

    [TEL NO] as ServiceID,

    [MRC PHP] as MRC,

    [OTC PHP] as OTC,

    [DSR DATE] as ActivationDate

FROM

(ooxml, embedded labels, table is incremental_fixed)

WHERE(TRANSACTION like 'ACTIVATION')

and [DSR DATE] >= '8/1/2016';

///$tab GSR

GSR:

LOAD [FA ID/Account Number],

    [Account Name],

    [Company Code],

    [Service Type],

    [Line Type],

    [Line Type Cascade],

    [Total August],

FROM

(ooxml, embedded labels, table is _xlnm._FilterDatabase);

QUALIFY *;

///$tab Missing

Missing:

NoConcatenate

LOAD Acct#,

AcctName,

ServiceID,

[PRODUCT GROUP],

MRC,

OTC,

ActivationDate

Resident Activations

Where not Exists([FA ID/Account Number], Acct#);

UNQUALIFY *;

DROP Table GSR;

[#5]

Thanks again for replying.  Wasn't working at first, then I decided to drop the NoConcatenate and the Drop Table functions. Now it is returning the missing accounts from the GSR table.  The QUALIFY function helped in identifying the missing accounts.

///$tab Data

Activations:

LOAD [ACCOUNT NAME] as AcctName, 

     [COMing DATE] as ActivationDate,

     [PRODUCT GROUP] as Product,

     [CIRCUIT ID] as ServiceID,

     [ACCOUNT #] as Acct#,

     MRC,

     OTC     

FROM

(ooxml, embedded labels, table is incremental_data)

WHERE(TRANSACTION like 'ACTIVATION')

and [COMing DATE] >= '8/1/2016';

///$tab Fixed

LOAD [APPLICANTS NAME] as AcctName,

     [PRODUCT GROUP] as Product,

     [ACCT NO] as Acct#,

     [TEL NO] as ServiceID,

     [MRC PHP] as MRC,

     [OTC PHP] as OTC,

     [DSR DATE] as ActivationDate

FROM

(ooxml, embedded labels, table is incremental_fixed)

WHERE(TRANSACTION like 'ACTIVATION')

and [DSR DATE] >= '8/1/2016';

///$tab GSR

GSR:

LOAD *

FROM

(ooxml, embedded labels, table is _xlnm._FilterDatabase);

QUALIFY*;

///$tab Missing

Missing:

LOAD Acct#,

    AcctName,

    ServiceID,

    ActivationDate,

    Product,

     MRC,

     OTC

Resident Activations    

where not Exists ([FA ID/Account Number],Acct#);

UNQUALIFY*;

[sunny_talwar]

Awesome, I am glad you were able to figure it out.

Best,

Sunny