topic Retrieve Month from text in QlikView

Retrieve Month from text

Tue, 19 Jan 2016 14:31:23 GMT

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 ?

Re: Retrieve Month from text

settu_periasamy — Tue, 19 Jan 2016 15:24:38 GMT

Try

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

Re: Retrieve Month from text

miguelbraga — Tue, 19 Jan 2016 15:28:41 GMT

This?

Re: Retrieve Month from text

Wed, 20 Jan 2016 08:27:16 GMT

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

sunny_talwar — Wed, 20 Jan 2016 09:25:01 GMT

Couple of ways:

Replace(FieldName, 'Value', '')

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

Re: Retrieve Month from text

manojkulkarni — Wed, 20 Jan 2016 09:42:18 GMT

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.

Re: Retrieve Month from text

Wed, 20 Jan 2016 10:01:35 GMT

Hi,

You can use following script

let s2 = '-1234567890.';

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

ValueMonth

1Jan

23Fev

456Mar

7890Abr

];

Re: Retrieve Month from text

Wed, 20 Jan 2016 15:34:54 GMT

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.

Re: Retrieve Month from text

Wed, 20 Jan 2016 15:35:28 GMT

Small change:

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

Re: Retrieve Month from text

settu_periasamy — Wed, 20 Jan 2016 15:44:10 GMT

Hi,

Did you the others suggestion?

May be the complete code like

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

Re: Retrieve Month from text

MarcoWedel — Thu, 21 Jan 2016 01:02:47 GMT

You might also have to set your LongMonthNames variable to your local month names to make the Date#( ... ,'MMMM') approach work.

regards

Marco

Re: Retrieve Month from text

MarcoWedel — Thu, 21 Jan 2016 01:03:42 GMT

another one:

Subfield(YourField, 'Value', 2)

regards

Marco