Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
sagarpawar
New Contributor III

Monthname and monthnumber to date conversion

HI,

Can you please help me on below scenario.

In one table I have month name as 'January', 'February',...  and in another table we have month number as 1,2,3....

I want to convert both into date (1st date of the month) as 01/01/Year,01/02/Year,01/03/Year...

How we will achieve that one?

It will really helpful.

 

Note='We have to fetch year from current date of calendar i.e. 2019'

Thanks,

Sagar

SGP
2 Solutions

Accepted Solutions
Highlighted

Re: Monthname and monthnumber to date conversion

May be try this

The table with Month Names (January, February,... etc)

Date(MakeDate(YearField, Month(Date#(MonthName, 'MMMM')), 1)) as Date

The table with Month Numbers (1, 2, 3... etc)

Date(MakeDate(YearField, Month(Date#(MonthName, 'M')), 1)) as Date

View solution in original post

Partner
Partner

Re: Monthname and monthnumber to date conversion


@sunny_talwar wrote:

May be try this

The table with Month Names (January, February,... etc)

Date(MakeDate(YearField, Month(Date#(MonthName, 'MMMM')), 1)) as Date

The table with Month Numbers (1, 2, 3... etc)

Date(MakeDate(YearField, Month(Date#(MonthName, 'M')), 1)) as Date

To use @sunny_talwar suggestion date#(MonthName ,'MMMM') you will need to make sure that your LongMonthNames-variable is defined correctly.

 

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

3 Replies
volakakis
New Contributor II

Re: Monthname and monthnumber to date conversion

Hi Sagar

date('01/' & if(MONTH = 'January',1,MONTH)& '/2019','DD/MM/YYYY') as NewField

Just expand the if statement to all months


Nikos

Highlighted

Re: Monthname and monthnumber to date conversion

May be try this

The table with Month Names (January, February,... etc)

Date(MakeDate(YearField, Month(Date#(MonthName, 'MMMM')), 1)) as Date

The table with Month Numbers (1, 2, 3... etc)

Date(MakeDate(YearField, Month(Date#(MonthName, 'M')), 1)) as Date

View solution in original post

Partner
Partner

Re: Monthname and monthnumber to date conversion


@sunny_talwar wrote:

May be try this

The table with Month Names (January, February,... etc)

Date(MakeDate(YearField, Month(Date#(MonthName, 'MMMM')), 1)) as Date

The table with Month Numbers (1, 2, 3... etc)

Date(MakeDate(YearField, Month(Date#(MonthName, 'M')), 1)) as Date

To use @sunny_talwar suggestion date#(MonthName ,'MMMM') you will need to make sure that your LongMonthNames-variable is defined correctly.

 

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post