Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Date format

Hello Everyone!

How to obtain dates in following format:

1st January 2016

2nd January 2016

3rd January 2016

4th January 2016

Thanks & Regards.

1 Solution

Accepted Solutions
Highlighted
Master II
Master II

I would work with a mapping table:

mapDates:

MAPPING LOAD * INLINE [read, show

1, 1st

2, 2nd

3, 3rd,

21, 21st

22, 22nd

23, 23rd

31, 31st];

and then to use

...

DUAL(APPLYMAP('mapDates', DAY(myDate), DAY(MyDate) & 'th') & ' ' & DATE(myDate, 'MMMM') & ' ' & YEAR(MyDate), MyDate)          AS LongDate

HTH

Peter

View solution in original post

3 Replies
Highlighted
Specialist
Specialist

Hello!

try this:

day(DATE) &

if(match(day(DATE),1,21,31) , 'st',

     if(match(day(DATE),2,22) , 'nd',

          if(match(day(DATE),3,23) , 'rd', 'th'))) & ' ' &

MonthName(DATE) & ' ' & year (DATE)

Sure that format LongMonthNames is 'January;February;March;April;May;June;July;August;September;October;November;December';

Highlighted
Master II
Master II

I would work with a mapping table:

mapDates:

MAPPING LOAD * INLINE [read, show

1, 1st

2, 2nd

3, 3rd,

21, 21st

22, 22nd

23, 23rd

31, 31st];

and then to use

...

DUAL(APPLYMAP('mapDates', DAY(myDate), DAY(MyDate) & 'th') & ' ' & DATE(myDate, 'MMMM') & ' ' & YEAR(MyDate), MyDate)          AS LongDate

HTH

Peter

View solution in original post

Contributor III
Contributor III

I thought that we had special format of date to get that st/nd/rd/th part. Anyways, both the answers above are helpful...

Thanks guys for your contribution.