topic Re: How to change the Date format for all Date fields in Memory? in QlikView

How to change the Date format for all Date fields in Memory?

cheburashka — Thu, 04 Jun 2015 10:10:29 GMT

Hello,

I load a qvd with multiple dates. All the dates have the Date Format DDMMMYYYY and are dual values. How can I change the date representation for all those dates to DD/MM/YYYY without writing code for each date individually or changing the date representation in document properties>number for each date individually?

Check the example files

,Thx in advance, Koen

Re: How to change the Date format for all Date fields in Memory?

sunny_talwar — Thu, 04 Jun 2015 10:16:08 GMT

What about this:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff]';

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='ma;di;wo;do;vr;za;zo';

LOAD

Date(Date1) as Date1,

Date(Date2) as Date2

FROM

[DatesTemp.qvd]

(qvd);

Re: How to change the Date format for all Date fields in Memory?

Kushal_Chawda — Thu, 04 Jun 2015 10:21:13 GMT

date(Date1,'DD/MM/YYYY') as Date1

date(Date2,'DD/MM/YYYY') as Date2

Re: How to change the Date format for all Date fields in Memory?

cheburashka — Thu, 04 Jun 2015 10:56:34 GMT

I already found this solution. This is not what I'm looking for. I would like to change all the date formats for the date fields that are currently in memory without having to write code for each field individually. I need to load 50 tables and convert all the date fields. Because the data is read from qvds qlikview can already recognize which fields are date fields without telling so in the script, but the issue is that the format is wrong. It would be nice if the procedure would be more automated than writing code for each individual field.

,Kr Koen

Re: How to change the Date format for all Date fields in Memory?

sunny_talwar — Thu, 04 Jun 2015 11:42:33 GMT

I know it is still an hassle, but in my experience I haven't come across an easier way to convert all dates without using a date function on each of them. The best you can do is what I mentioned:

SET DateFormat='DD/MM/YYYY';

LOAD

Date(Date1) as Date1,

Date(Date2) as Date2

FROM

[DatesTemp.qvd]

(qvd);

Note: Since you have set your DateFormat at the beginning, you don't have to specify format within your Date() function.

HTH

Best,

Sunny

Re: How to change the Date format for all Date fields in Memory?

swuehl — Thu, 04 Jun 2015 11:56:01 GMT

I don't think that there is a built in feature to apply a date format to all date fields with the click of a button.

Maybe you can write a macro, that loops over all fields (filtererd by date tag) and applies the date formatting.