2 Replies Latest reply: Oct 30, 2016 4:55 PM by Marco Wedel RSS

    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

    Fernando Mercado

      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

      [I:\calidad venta\Aperturas\Cuentas Corrientes * Zonal.xlsx]

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

       

      LEFT JOIN (APERTURAS)

       

      LOAD RutNUM,  

           Direccion,

           Comuna,

           Ciudad,

           [Numero Tarjeta],

           DateActivation

      FROM

      [M:\TCRESIMKT_$(vhoy).CSV]

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