topic Re: Formatter date in QlikView

Formatter date

intergnek — Mon, 26 Jan 2026 18:19:17 GMT

Bonjour communauté,

cela peut paraître basique mais j'ai besoin de votre aide.

Après chargement d'un fichier Excel, les dates apparaissent dans un format à 5 chiffres.

J'ai essayé cette méthode Date(jour, 'DD/MM/YYYY'), mais ca ne marche pas.

Exemple : 01/01/2017 dans Excel apparaît 42736 dans Qlik.

J'ai besoin d'une fonction ou d'un moyen pour formater ce nombre 42736 afin qui revienne sous le format initial c'est-à-dire 01/01/2017 dans Qlik.

Merci d'avance!

Re: Formatter date

Anonymous — Fri, 29 Dec 2017 17:26:44 GMT

Have you tried Load editor ?

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

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

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD/MM/YYYY';

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

SET FirstWeekDay=6;

SET BrokenWeeks=1;

Re: Formatter date

intergnek — Fri, 29 Dec 2017 17:39:09 GMT

When i do like that Date(mydate, $(DateFormat)), it doesn't succeed

Re: Formatter date

maxgro — Fri, 29 Dec 2017 19:49:56 GMT

Could you post the script or the document?

I tried this

LET vformat = 'DD/MM/YYYY';

SET DateFormat='DD/MM/YYYY';

LOAD

Field1,

Date(Field1, 'DD/MM/YYYY') as Field1Date,

Date(Field1, '$(vformat)') as Field1Date2,

Date(Field1, '$(DateFormat)') as Field1Date3

FROM Cartel2.xlsx (ooxml, embedded labels, table is Foglio1);

and it works

This also can help with date problem

QlikView Date fields

Re: Formatter date

pradosh_thakur — Fri, 29 Dec 2017 20:23:22 GMT

may be this

Date(mydate, '$(DateFormat)') in script

Re: Formatter date

Peter_Cammaert — Mon, 01 Jan 2018 21:24:49 GMT

Remember that in QlikView a properly stored date field is a dual field. It has both a numerical and a text value. QlikView uses the same binary Date values as Excel. So the numerical value will still be binary 42736 and the initial text value will be whatever is set for your document in your script (if you read Excel values using defaults) or whatever you assign as formatting stirng with Date(NumValue, FormatString).

Num(DateField) will return the binary value that is the same in Excel and in QlikView.

Text(DateField) will return the value formatted using a formatting string (either the default DateFormat or whatever you used in a two-parameter Date() call)

Also note that Date() is a formatting function, not an interpretation function. So Date(42736, 'D/MM/YYYY') should produce a valid date of 1/01/2017.