0 Replies Latest reply: Sep 3, 2018 5:16 PM by Diego Alejandro Velez Becerra RSS

    Buffer (Incremental) Load with Left and Outer Join

    Diego Alejandro Velez Becerra

      Hi, Good day to all of you

       

      I come to you asking for help so I can make my application load times more efficient.

       

      I have an app that needs to be updated every month with different reports generated by automatic odt's from the comercial system of the company I'm working in.

       

      I'm using the buffer (Incremental) Load in the script so every month it updates only the new files that are added, The problem is some of the data in the script is added with Left Join and Outer Join, so the Buffer load doesn't work with this ones. (I need to do it like that because "Cartera" report has some null values in some fields by error and "Ventas" report helps complimenting this data but also "Ventas" report has users that aren't included in "Cartera" report and viceversa so they compliment each other)

       

      My question is how can I modify my script so it only updates new files even if they are added with Outer and Left Joins.

       

      Thanks a Lot.

       

      I'll share my script with you (The fields in red are the ones that have some null values by error, maybe it could be important):

       

      Cartera:

      Buffer (Incremental) LOAD

          SUCURSAL,

          ESTRATO,

          USUARIO,

          RUTA,

          SECTOR,

          ZONA,

          MUNICIPIO,

          ATRASOS,

          CONSUMO,

          CATEGORIA,

          CREDITOS,

          "0_0",

          "1_1",

          "2_2",

          "3_3",

          "4_4",

          "5_5",

          "6_6",

          "7_7",

          "8_8",

          "9_9",

          "10_10",

          "11_11",

          "12_12",

          "13_24",

          "25_36",

          "37_MAS",

          CARTERA_CORRIENTE,

          CARTERA_VENCIDA,

          "TOTAL",

          filebasename() as PERIODO

      FROM [lib://COMERCIAL_CARTERA/*.csv*]

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

       

      Clase_Servicio:

      Left join (Cartera)

      Buffer (Incremental) LOAD

          NUMERO_CLIENTE as USUARIO,

          subfield(CLASE_SER,'-',1) as CLASE_SER,

          filebasename() as PERIODO

      FROM [lib://CLASS/*.csv*]

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

       

      Clasifica_clase:

      LOAD

          CLASE_SER,

          "CLASE DE SERVICIO"

      FROM [lib://clasi_CLASS/clase_ventas2.csv]

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

       

      Ventas:

      Outer Join (Cartera)

      Buffer (Incremental) LOAD

      NUMERO_CLIENTE as USUARIO,

      SUCURSAL,

      PERIODO,

      CLASE as CLASE_SER,

      MUNICIPIO as COD_MUNICIPIO,

      DESCRIPCION as MUNICIPIO,

      SECTOR,

      ESTRATO,

      alt(NIVEL_TENSION,1) as "NIVEL DE TENSION",

      CONSUMO_ACTIVA,

      VALOR_CONSUMO_ACTIVA,

      VALOR_COMPENSACION,

      VALOR_REACTIVA,

      DIAS_FACTURADOS,

      FECHA_EXPEDICION_FACTURA,

      UBICACION,

      FECHA_INI_PERIODO_FACTURACION,

      FECHA_FIN_PERIODO_FACTURACION

      FROM [lib://VENTAS/*.txt*]

      (txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

       

      Metas:

      LOAD

          PERIODO,

          META_MOR,

          ROJO_MOR,

          META_MORSS,

          ROJO_MORSS,

          META_vs_TOT,

          ROJO_vs_TOT,

          META_vs_ING,

          ROJO_vs_ING

      FROM [lib://METAS/*.xlsx*]

      (ooxml, embedded labels, header is 1 lines, table is Hoja1);

       

      Usuarios:

      Buffer (Incremental) LOAD

          PERIODO,

          SUCURSAL,

          MUNICIPIO,

          UBICACION,

          ESTRATO,

          "SUM(C.CANTIDAD)" as USUCAN,

          "CLASE_SER"

      FROM [lib://CLIENTES/*.txt*]

      (txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

       

      Thanks a lot for reading.

       

      Cordially,

      Diego Vélez

      Qlik Sense noob.