Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Retrieve Month from text

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 ?

11 Replies
settu_periasamy
Master III
Master III

Try

Month (date#(right (field,3),'MMM'))

miguelbraga
Partner - Specialist III
Partner - Specialist III

This?

Screenshot_1.png

Not applicable
Author

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

sunny_talwar

Couple of ways:

Replace(FieldName, 'Value', '')

or

Mid(FieldName, 6, Len(FieldName) - 4)

manojkulkarni
Partner - Specialist II
Partner - Specialist II

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.

Not applicable
Author

Hi,

You can use following script

let s2 = '-1234567890.';

LOAD *,PurgeChar(ValueMonth, $(s2)) as Month INLINE [

    ValueMonth

    1Jan

    23Fev

    456Mar

    7890Abr

];

Not applicable
Author

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.

Not applicable
Author

Small change:

TextBetween(Mapsubsting('Map_month',UPPER(FieldName)),'<','>',1) AS MonthName

settu_periasamy
Master III
Master III

Hi,

Did you the others suggestion?

May be the complete code like

Month (date#(Replace(Field, 'Value', ''),'MMMM')) as Month