Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Retrieve Month from text

Try

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

miguelbraga
Valued Contributor III

Re: Retrieve Month from text

This?

Screenshot_1.png

Not applicable

Re: Retrieve Month from text

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

Re: Retrieve Month from text

Couple of ways:

Replace(FieldName, 'Value', '')

or

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

manojkulkarni
Valued Contributor II

Re: Retrieve Month from text

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

Re: Retrieve Month from text

Hi,

You can use following script

let s2 = '-1234567890.';

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

    ValueMonth

    1Jan

    23Fev

    456Mar

    7890Abr

];

Not applicable

Re: Retrieve Month from text

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

Re: Retrieve Month from text

Small change:

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

Re: Retrieve Month from text

Hi,

Did you the others suggestion?

May be the complete code like

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

Community Browser