topic Re: Where Not exist in QlikView

Where Not exist

Thu, 14 Aug 2014 08:03:25 GMT

I Load table with a lot of informtaion about person , his name , date of birth and

So for example in my table Pivot I have this Information

Pivot table :

Name	ID SS
AARON DAVIS	1
MATHIEU LAJEARS	2
NICOLAS TRAD	3
Bill Lewis	4

HSBC :

Name	ID SS
LEBRON MICK	5
Lestlie Desparado	6
Nathan Like	7
AARON DAVIS	1
MATHIEU LAJEARS	2
NICOLAS TRAD	3
Bill Lewis	4

I want to detect the personne how exist in HSBC and dont exist in pivot table ( for this example is Lebron leslie and nathan)

Thanks

Re: Where Not exist

tresB — Thu, 14 Aug 2014 08:18:08 GMT

Like attached sample?

Re: Where Not exist

ashfaq_haseeb — Thu, 14 Aug 2014 08:25:06 GMT

Hi,

Try below.

HSBC:

Load * inline

[

Name,IDSS

AARONDAVIS,1

NICOLASTRAD,2

BillLewis,

];

NoConcatenate

Pivot:

Load * inline

[

Name,IDSS

AARON,DAVIS,1

MATHIEULAJEARS,2

NICOLASTRAD,3

BillLewis,

]

where IDSS <> '' and not Exists(Name);

drop table HSBC;

Regards

ASHFAQ

Re: Where Not exist

Thu, 14 Aug 2014 08:35:50 GMT

Sorry i badly explain my problem , i have updat my thread please see it thank u

Re: Where Not exist

jagan — Thu, 14 Aug 2014 08:36:23 GMT

Hi Ben,

Try this script

HSBC:

Load * inline

[

Name,IDSS

AARONDAVIS,1

NICOLASTRAD,2

BillLewis,

];

Pivot:

LEFT KEEP(HSBC)

Load

Name, IDSS AS IDSS_Pivot

inline

[

Name,IDSS

AARONDAVIS,1

MATHIEULAJEARS,2

NICOLASTRAD,3

BillLewis,

];

Regards,

Jagan.

Re: Where Not exist

tresB — Thu, 14 Aug 2014 08:42:14 GMT

Try like:

Tab1:
LOAD Name,
[ID SS] as SS_OLD
FROM
[http://community.qlik.com/thread/129467]
(html, codepage is 1252, embedded labels, table is @1);

Tab2:
LOAD Name,
[ID SS]

FROM
[http://community.qlik.com/thread/129467]
(html, codepage is 1252, embedded labels, table is @2) where not Exists(SS_OLD, [ID SS]);

Drop table Tab1;

Re: Where Not exist

jagan — Thu, 14 Aug 2014 08:52:14 GMT

Hi Ben,

Try below script arrived a flag for not exist users in script use that field to identify exists and not exists users

Temp:

LOAD Name,

[ID SS]

FROM

[http://community.qlik.com/thread/129467]

(html, codepage is 1252, embedded labels, table is @2);

Left Join

LOAD Name,

[ID SS] as SS_OLD

FROM

[http://community.qlik.com/thread/129467]

(html, codepage is 1252, embedded labels, table is @1);

Data:

LOAD

If(IsNull(SS_OLD), 'Not Exists', 'Exists') AS Flag

RESIDENT Temp;

DROP TABLE Temp;

Regards,

Jagan.