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

[#1]

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

[el_aprendiz111]

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