Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. 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

Highlighted
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.