Formatter date

[intergnek]

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!

[Anonymous]

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;

[intergnek]

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

[maxgro]

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

[pradosh_thakur]

may be this

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

[Peter_Cammaert]

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.