Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
When i do like that Date(mydate, $(DateFormat)), it doesn't succeed
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
may be this
Date(mydate, '$(DateFormat)') in script
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.