Error in script

[#1]

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

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

[stephencredmond · Accepted solution]

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

[#3]

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.