2 Replies Latest reply: Apr 18, 2011 4:08 AM by Carlos Ayuso RSS

    Creating a new field on script

      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!!

        • Creating a new field on script
          Jean-Jacques Jesua

          Hi Alberto

          I don't understand why you have to create a new field in the script. I think it's better to display the rotation in a chart.

          I suggest you to create a field date for each file :

          From inventory : Makedate( Ano , Mes , 1 ) as Date

          From sale : Makedate ( Year([Anno cal./mese]) , Month([Anno cal./mese]) , 1) as Date

          So your 2 table are now linked by the date field.

          Then create a chart with

          Dimension : MonthNames (Date)

          Expression : Sum (Venta) / Sum( [Qtà stk. val.])

           

          Hope it helps you

           


            • Creating a new field on script

              Hi,

              I already have my Qlikview with few charts using the "Rotación" as expression. The fact is that "Rotación" is the most important parametre in my study, and i have to make pivot tables, charts and i also want to be able to select intervals in my "Rotacion" parameter.

              If i achieve to create "Rotación" as a field, it would simplify my Qlikview giving me more options for study.

              Does someone know how to create the field "Rotación" in the initial script??

               

              Thanks again for your time!!