Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How can I left join two tables with an ID in Common but different date of activation accounts, I want to left join each date in the original table with the closest

First I used left Join to join the ID (RutNUM) to different accounts ([Numero Tarjeta]), but in the case that a Client have 2 or 3 accounts, the values are related by the ID and not the dates of register (FECAPE) and activation (DateActivation) so for each date of register, qlikview paste the two or three dates of activation, how can I paste the register choosing only the closest Activation date (the next) one to relate the dates and se the relation. Thanks.


APERTURAS:

LOAD RUT,

     num(LEFT(RUT, LEN(RUT)-1))                                                                       as RutNUM,

     [NOMBRE CLIENTE],

     SUP,

     DATE(FAPE) as FAPE, 

     left(DATE(FAPE),4)&mid(DATE(FAPE),6,2)&right(Date(FAPE),2)                     as FECAPE,

     SUCURSAL,

     SEGMENTO,

     EJECUTIVO,

     TIPO,

     MES,

     CLASE,

     NETO,

     if(num(num(LEFT(RUT, LEN(RUT)-1)))<=50000000,'PN','OTRO')                       as TIPO_PERSONA,

     LEFT(DATE(FAPE),4)&mid(DATE((FAPE)),6,2)                                              as [AÑOMES APERTURA]

FROM

(ooxml, embedded labels, table is hoja1) where RUT <> 'NO' and CLASE = 'APERTURA';

LEFT JOIN (APERTURAS)

LOAD RutNUM,  

     Direccion,

     Comuna,

     Ciudad,

     [Numero Tarjeta],

     DateActivation

FROM

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

2 Replies
el_aprendiz111
Specialist
Specialist

Hi,

Would you be able to share a sample with the expected output?

MarcoWedel

Hi,

you could join only using the ID field, generate an interval field for each date of register/activation combination, calculate the minimal absolute difference (e.g. like "Min(Fabs([date of register]-[date of activation ]))  ")  and keep only the minimal intervals.

hope this helps

regards

Marco