Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Highlighted
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

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
Vegar
Valued Contributor

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';

 

 

Please ekskuse my Norglish and Swenglish typos.
3 Replies
volakakis
New Contributor

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

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
Vegar
Valued Contributor

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';

 

 

Please ekskuse my Norglish and Swenglish typos.
Community Browser