Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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.