7 Replies Latest reply: Apr 8, 2011 12:31 PM by new_user3 RSS

    Date format

    Gerhard Laubscher

      Hi,

      I'm importing a daily account extract into a qlikview document.

      The date format on the account extract is DD-MMM-YY, where the MMM is text. So today's date will show as 08-Apr-11.

      Qlikview doesn't recognize this as a date, only as text. What can I do to change this?

      Thanks

        • Date format
          Miguel Angel Baeyens de Arce

          Hello Gerhard,

          Try the following in your load script:

           

          Date(Date#(DateField, 'DD-MMM-YY')) AS DateField


          Hope that helps.

            • Date format
              Gerhard Laubscher

              Hi Miguel,

              Sorry for being a Qlikview spaz - but could you be more specific?

              Where exactly whould I put that in the script? SER DateFormat?

              Also, when you say 'datefield' am I suppose to enter the names of the fields there? Because the extract has about 20 different date fields.

               

                • Date format
                  Miguel Angel Baeyens de Arce

                  Hello,

                  Your script (the piece of code you need to write to pull data from the database and put into memory) will look like the following

                   

                  Table: // table labelLOAD TransID, // this are the fields you are loading into QlikView's memory CustID, Date(Date#(DateField, 'DD-MMM-YY')) AS DateField;SQL SELECT TransID, CustID, DateFieldFROM Table.Datasource;


                  The above is just an example just to see how to do the conversion in the load script, so change it to fit your needs.

                  Hope that makes more sense.

                • Date format

                   


                  Miguel Angel Baeyens wrote:
                  Hello Gerhard,
                  Try the following in your load script:
                  <blockquote><pre>Date(Date#(DateField, 'DD-MMM-YY')) AS DateField

                  Hope that helps.

                   

                  You need to do that in the load script and you and you will have to swap DateField for your date fields, and probably do it for each of you date fields

                   

                  Regards

                  Jon

                    • Date format
                      Gerhard Laubscher

                      My load script looks like this:

                      SET ThousandSep=',';
                      SET DecimalSep='.';
                      SET MoneyThousandSep=',';
                      SET MoneyDecimalSep='.';
                      SET MoneyFormat='R #,##0.00;R-#,##0.00';
                      SET TimeFormat='hh:mm:ss TT';
                      SET DateFormat='YYYY/MM/DD';
                      SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff] TT';
                      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
                      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                      Directory;
                      LOAD [Product Name],
                      [ID number],
                      [Account no],
                      [Card no],
                      [Account open date],

                      and then the rest of my field names, and then

                      FROM
                      [Account Extracts\Daily_AccountExtract_A_07-APR-2011_08042011015858.txt]
                      (txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines);

                      So if I have a field named [Account Open Date], where exactly should I enter Date(Date#([Account Open Date], 'DD-MMM-YY')) AS [Account Open Date]

                      ?

                      \Thanks