5 Replies Latest reply: Jan 1, 2018 4:24 PM by Peter Cammaert RSS

    Formatter  date

    Poté Amaud GNEKOHI

      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
          Allu Allu

          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
            Massimo Grossi

            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

            1.png

             

            This also can help with date problem

            QlikView Date fields

            • Re: Formatter  date
              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.