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.
