topic how convert text (January) to month (1) in QlikView

how convert text (January) to month (1)

Tue, 26 Oct 2010 10:20:26 GMT

hi friends,

how can I convert text "January" (February, ...) to month: 1 (2, ...) ?

Detail info:
in datasource is column "MyMonthName" (with values: January, February, March, ... ) and column "MyMonthID" (with related values: I, II, III, IV, ..., XII)
Now I want after select of month "January" (in QlikView object ListBox with field "MyMonthName") and select of year (eg.2010) (in object Year) create text with date "1.1.2010"

how convert text (January) to month (1)

lucas4bi — Tue, 26 Oct 2010 10:50:21 GMT

Hello,

if you dont wanna change your SET MonthNames format (which would automatically makes u able to compare the month "February" with number 2 for example) then you could use this:

Month_Number:

LOAD distinct

RECNO() AS Month_Number,

MyMonthName

resident MyTable while RecNo()<=12;

This way you can use Month_Number when u need to compare the month as text, having it associated with MyMonthName.

how convert text (January) to month (1)

Tue, 26 Oct 2010 10:53:05 GMT

I find solution:

=num(month(date#(MyMonthName,'MMM')))

how convert text (January) to month (1)

jonathandienst — Tue, 26 Oct 2010 11:09:29 GMT

Juraj

The Month() function should do what you need. It returns a dual value, with a text representation of Jan, Feb, Mar... and a numeric value 1, 2, 3.If your data table also contains a Year field (like the script below), then you can select records for that month and year if you create listboxes for MyMonthName and MyYearName:

DATATABLE:
LOAD TranDate AS Date,
 Month(TranDate) AS MyMonthName,
 Year(TranDate) AS MyYearName,
 ....
 ;
SQL SELECT
 TranDate,
 ...
FROM ...
;

If you must get the date as a value when the Month and Year are selected in listoxes, then I suggest that you create a variable like this in your script:

Set vMyDate = MakeDate(myYearName, MyMonthName);

Now vMyDate will contain 1.1.2010 when Jan and 2010 are selected. It will contain null if no month or no year is selected.

Hope that helps

Jonathan

how convert text (January) to month (1)

Wed, 21 Mar 2012 16:57:36 GMT

Hi JurajChovan!

For the text with the name of the month, is actually converted to a month value, try this:

Date( Date# ( MyMonthName,'MMM'),'MMM')

God bless you!

Stive

Brazil

Re: how convert text (January) to month (1)

manjunatha_av — Fri, 13 Dec 2019 06:23:59 GMT