2 Replies Latest reply: Mar 29, 2011 12:09 PM by Simon Saugier RSS

    Error in script

      Hi,

      I want to create some fileds from an Excel file. My file contains a column titled "Anno cal./mese" wich has the year and month in text format:

      GEN 2011 / FEB 2011 / MAR 2011 ...

      My intention is to convert this information into two new created fields, Año & Mes.

      In the loading script i wrote the following:

      LOAD Materiale,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      [Quantità PF] as Venta

       

      FROM

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

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

      The script loads OK, but both new fields Año & Mes contain only values 'ERROR' & 'Actualizar Scrpit'

      Can somenone help me find were is the error or make any comment?

      I appreciate your help,

      Thanks!

       

        • Error in script
          Stephen Redmond

          Hi Alberto,

          You can't compare a value with a wildcard using an =

          What you need to do for that is to use WildMatch:

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

          You can also convert that text to a date using date#:

          Date#([Anno cal./mese], 'MMM YYYY')

          With a date value, you can use the MMMMM format patern to get the long month description.

           

          Regards,

           

           

          Stephen

           

           

          • Error in script

            When you use the single quotes ('DIC*'), it creates a string literal. Your wildcard (asterisk *) is not doing what you want. It is literally checking if the value in the field is DIC*, whereas you want to see if it starts with DIC and then has some text after that.

            I recommend using the following String funcitons lo split your column into two separate columns, and then using the Map function to map to the correct month name:

            First, create a mapping table:

            Mes_Map:

            mapping load * inline [

            Abbr, Name

            GEN, Enero

            FEB, Febrero

            ...

            ];

             

            Next, you can use Subfield and ApplyMap to get the values you want.

            ApplyMap('Mes_Map', subfield([Anno cal./mese], ' ', 1)) AS Mes // Get the month (mes) name, based on the abbreviation

            subfield([Anno cal./mese], ' ', 2) AS Ano // Get the year (ano)

            The subfield function splits the string into different pieces. If you split on ' ' and select the first field, you will get your month. If you do the same split and select the second field, you will get the year.