Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Frquiroga1995
Contributor II
Contributor II

If a record exist in another table

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
SiniestroDNI
100138492213
100537568943
800944667894
9007650450
700542345809
1231232134567
4124123423678
3321314325887

 

 

PAGOS
OperaciónBENEF_DOC
4652638492213
765466774648678
234234456363546
786483563478
2342137568943
624753744667894
5363578423678
24623454325887
3 Replies
Chanty4u
MVP
MVP

try this

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

or

 

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

QFabian
Specialist II
Specialist II

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

QFabian
samuelyhh
Contributor II
Contributor II

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] ;
//