3 Replies Latest reply: Apr 18, 2011 9:44 AM by Carlos Ayuso RSS

    Create a new field in script using fields of different tables

       

      Hi,

      I am use a Qlikview which loads fields from different files. I have a file that has the inventory of every month, i load one table per month:

       

      LOAD Año,

      Mes,

      Materiale,

      [Qtà stk. val.],

      [Valore stk. v.]

       

      FROM

      [O:\Logistic\TEMAS STOCKS\INVENTARIOS\Informes mensuales\Informes Qlik\Informe Stocks 6A*.xlsx]

      (ooxml, embedded labels, table is [Inventario mes])

      Also i have a file that loads the sells of every month per product:

      LOAD Materiale,

      IF(WildMatch([Anno cal./mese],'GEN*'), 'Enero',

      IF(WildMatch([Anno cal./mese],'FEB*'), 'Febrero',

      IF(WildMatch([Anno cal./mese],'MAR*'), 'Marzo',

      IF(WildMatch([Anno cal./mese],'APR*'), 'Abril',

      IF(WildMatch([Anno cal./mese],'MAG*'), 'Mayo',

      IF(WildMatch([Anno cal./mese],'GIU*'), 'Junio',

      IF(WildMatch([Anno cal./mese],'LUG*'), 'Julio',

      IF(WildMatch([Anno cal./mese],'AGO*'), 'Agosto',

      IF(WildMatch([Anno cal./mese],'SET*'), 'Septiembre',

      IF(WildMatch([Anno cal./mese],'OTT*'), 'Octubre',

      IF(WildMatch([Anno cal./mese],'NOV*'), 'Noviembre',

      IF(WildMatch([Anno cal./mese],'DIC*'), 'Diciembre','ERROR')))))))))))) as Mes,

      IF(WildMatch([Anno cal./mese],'*200*') or WildMatch([Anno cal./mese],'*199*'),'Antiguo',

      IF(WildMatch([Anno cal./mese],'*2010'),'2010',

      IF(WildMatch([Anno cal./mese],'*2011'),'2011',

      IF(WildMatch([Anno cal./mese],'*2012'),'2012',

      IF(WildMatch([Anno cal./mese],'*2013'),'2013',

      IF(WildMatch([Anno cal./mese],'*2014'),'2014',

      IF(WildMatch([Anno cal./mese],'*2015'),'2015','Actualizar Scrpit'))))))) as Año,

      [Quantità PF] as Venta,

      [Luogo di spedizione]

       

      FROM

      [O:\MARKETING\Qlik_MKT\Vendes\vendes clients*.xls]

      (biff, embedded labels, table is Hoja1$);

      I want to create a field called 'Rotación' which is calculated as: --> (Venta/[Qtà stk. val.])

      This field 'Rotación' has to be the sales (Venta) of a specific Year(Año) & Month(Mes) divided by the stock amount ([Qtà stk. val.]) of the same Year & Month. Also observe that the Month & Year, in the sales file, are calculated fields (to make it them same format as the month in the inventory file)

      As the two fields that appear in the formula are provided from different files, i don't know how to make it in the script. Help!

       

      Thanks for your time!!