Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
cheburashka
Contributor III

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

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

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

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.

View solution in original post

5 Replies
Highlighted

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

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

Highlighted

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

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

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

Highlighted
cheburashka
Contributor III

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

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

Highlighted

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

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

Highlighted
MVP
MVP

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

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.

View solution in original post