Where Not exist

[#1]

Hy

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

[tresesco]

Like attached sample?

[ashfaq_haseeb]

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

[#4]

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

[jagan]

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.

[tresesco]

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;

[jagan]

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.