topic Date formate in QlikView

Date formate

Mon, 16 Jan 2012 10:01:31 GMT

Hi,

I have column temp which contain date in 'mm/dd/yyyy' formate and i want to change it in this 'Monday1^st January 2011' formate.

I have done every thing apart from this st or rd or th .

How can i apply this string with date.

Thanks in advance

Date formate

hic — Mon, 16 Jan 2012 11:40:44 GMT

First, add your day numbers as a mapping table:

DayNumbers:
Mapping Load * inline
[Day, DayNumber
1, 1st
2, 2nd
3, 3rd
...

You will need 31 rows in this table. Then, you can format the date by using e.g.

dual(WeekDay(DateField) &' '& applymap('DayNumbers', Day(DateField)) &' '& Date(DateField, 'MMM YYYY'), DateField)

Re: Date formate

Mon, 16 Jan 2012 12:42:27 GMT

Thanks for the answer

bt my problem is how to add st or nd or rd.

for it i have used

if(Date(Temp,'dd')=01 or Date(Temp,'dd')=21 or Date(Temp,'dd')=31,'st',if(Date(Temp,'dd')=02 or Date(Temp,'dd')=22,'nd',if(Date(Temp,'dd')=03 or Date(Temp,'dd')=23,'rd','th')))

this statement and save it into a variable but it is not working.

Date formate

hic — Mon, 16 Jan 2012 13:29:02 GMT

I think that the best way to add 'st', 'nd' and 'rd' is to use applymap in the way described above. You could of course instead have a mapping table where 1 is mapped to 'st' only (instead of '1st') but the basic solution will be very similar to the above one.

A nested if-function will also work. But, you should not use the comparison Date(Temp,'dd')=01. The Date function will format the date to (the string) '01', but its numeric value will still be forty thousand something. Use instead: Date(Temp,'dd')='01' (string comparison), or better: Day(Temp)=1 .