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

[cheburashka]

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

[sunny_talwar]

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);

[Kushal_Chawda]

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

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

[cheburashka]

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

[sunny_talwar]

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

[swuehl · Accepted solution]

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.