topic Re: Where not exists in QlikView

Where not exists

Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Where not exists

sunny_talwar — Tue, 27 Sep 2016 16:12:56 GMT

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;

Re: Where not exists

Wed, 28 Sep 2016 00:18:12 GMT

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

Re: Where not exists

sunny_talwar — Wed, 28 Sep 2016 17:32:16 GMT

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;

Re: Where not exists

Thu, 29 Sep 2016 02:56:43 GMT

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

Re: Where not exists

sunny_talwar — Thu, 29 Sep 2016 15:16:04 GMT

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

Best,

Sunny