Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ahbzshk07
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
prieper
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
t_chetirbok
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';

prieper
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

ahbzshk07
Contributor III
Contributor III
Author

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.