Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good evening,
I I have to retrieve month from text column . The column is labelled
'ValueJan'
'ValueFeb'
'ValueMar'
..
...
..
and soo on.
How I can correctly retrieve the month ?
Try
Month (date#(right (field,3),'MMM'))
This?
Thanks a lot for the help,
it's possible to make the same thing but from the left?
In my precedent post I have insert wrong month names for error. The real textcolumn have the month in italian, like this
ValueGennaio,
ValueFebbraio,
ValueMarco,
...
the best for me it's truncate the part 'Value',and calculate month for the remaining part
Couple of ways:
Replace(FieldName, 'Value', '')
or
Mid(FieldName, 6, Len(FieldName) - 4)
You can use replace function to get MonthName.
Replace(FieldName, 'Value', '')
Later create inline table with MonthName & month number, so you will get month number as well.
Hi,
You can use following script
let s2 = '-1234567890.';
LOAD *,PurgeChar(ValueMonth, $(s2)) as Month INLINE [
ValueMonth
1Jan
23Fev
456Mar
7890Abr
];
If your input data field value always static string with month name then you can use the Replace function to extract the Month name.
Trim(Replace(FieldName,'Value','')) AS MonthName
If your string value varies for each value then you can do with mapping table & mapsubsting function like below:
Map_Month:
Mapping
LOAD * INLINE [
Input, Value
JAN,<Jan>
FEB,<Feb>
MAR,<Mar>
APR, <Apr>
.
.
.
]
;
LOAD
FieldName,
TextBetween(Mapsubsting('Map_month',FieldName),'<','>',1) AS MonthName
From Source;
Note: You can change the map table according to your input values.
Small change:
TextBetween(Mapsubsting('Map_month',UPPER(FieldName)),'<','>',1) AS MonthName
Hi,
Did you the others suggestion?
May be the complete code like
Month (date#(Replace(Field, 'Value', ''),'MMMM')) as Month