If a record exist in another table

[Frquiroga1995]

Hi !

I am doing some analysis from different databases. What I need to do is to see which ones of the records from DNI field, from the database CARTERA, exists in the field BENEF_DOC from the database PAGOS. For example, 38492213 and 37568943 exists in both tables. (In Cartera they are in the DNI field, and in Pagos they are in the BENEF_DOC field).

The idea is to create a new field in Cartera, that shows a number 1, if records from DNI exists in BENEF_DOC from Pagos. And a number 0 if they don't exist.

Thank you very much !! I would really appreciate if someone could help me with this. 

Cartera
Siniestro	DNI
1001	38492213
1005	37568943
8009	44667894
9007	650450
70054	2345809
123123	2134567
4124123	423678
332131	4325887

PAGOS
Operación	BENEF_DOC
46526	38492213
76546	6774648678
234234	456363546
78648	3563478
23421	37568943
6247537	44667894
5363578	423678
2462345	4325887

[Chanty4u]

try this

if(wildmatch(Concat(BENEF_DOC,','),'*' & DNI &'*'),'YES','NO')

or

if(wildmatch(Concat(BENEF_DOC,','),'*' & DNI &'*'),0,1)

[QFabian]

Hi @Frquiroga1995 , please try something like this :

PAGOS:
Load
Operación,
BENEF_DOC
from PAGOS;

left join

//Cartera:
Load
DNI as BENEF_DOC,
Siniestro,
1 as Exists
FROM CARTERA
where
exists(BENEF_DOC, DNI);

[samuelyhh]

Hi,

 you may try this.

 [PAGOS]:
LOAD * INLINE [
Operación, BENEF_DOC
46526, 38492213
76546, 6774648678
234234, 456363546
78648, 3563478
23421, 37568943
6247537, 44667894
5363578, 423678
2462345, 4325887
];
//
[Cartera]:
LOAD * INLINE [
Siniestro, DNI
1001, 38492213
1005, 37568943
8009, 44667894
9007, 650450
70054, 2345809
123123, 2134567
4124123, 423678
332131, 4325887
];
//
LEFT JOIN ([Cartera])
LOAD
BENEF_DOC AS DNI ,
1 AS EXIST_IN_PAGOS
Resident [PAGOS]
;
[Tmp01]:
NoConcatenate LOAD
Siniestro,
DNI ,
if (EXIST_IN_PAGOS = 1, EXIST_IN_PAGOS, 0) as EXIST_IN_PAGOS
Resident [Cartera]
;
drop Tables [Cartera];
RENAME Table [Tmp01] to [Cartera] ;
//