8 Replies Latest reply: Feb 15, 2016 11:22 AM by Sébastien Fatoux RSS

    Date column is not loading as "Date & Time"

    Ahammad Shafi

      Dear Experts:

       

      Please note, in the input excel file the date column is in "YYYYMM" formatInput Format.PNG. Here you can see that the name of the column is "F1.PERIOD".

       

      Hence I changed the date format in 'Data Load Editor' accordingly and then loaded the data. Data Load Editor.PNG.

       

      Yet, I don't see the period column while editing the sheets. It will be really helpful and appreciated if you kindly help me out to solve the problem.

      Sheet Items.PNG

       

      Ahammad Shafi

        • Re: Date column is not loading as "Date & Time"
          balraj ahlawat

          What Exactly you want?

           

          The format you changed in Set Default variable, Qlik engine will consider this when you use Date() function which is a format function...

           

          Say....Date(YourDateField) will give you the output in 'YYYYMM'  

          • Re: Date column is not loading as "Date & Time"
            Jonathan Dienst

            Changing the default is not the best way to read the date correctly. I expect the period is being read as a number in any case:

             

                 201512 = 20,512

             

            If that is the case, then simply formatting it as a date will not work correctly. To convert that to a proper date format, use something like:

             

                 Date(MakeDate(Floor(Period/100), Mod(Period, 100)), 'YYYYMM') As Period,

             

            As to why you don't see Period, without a sample of the source document (just a few lines will do) and your load script, its impossible to say.

            • Re: Date column is not loading as "Date & Time"
              Sébastien Fatoux

              Go to the help of Qlik Sense : Derived fields

               

              See th last paragraph "Use the derived date fields in a visualization".

               

              You need to add something like that to declare the field associated to your $date fields

              Calendar:

              DECLARE FIELD DEFINITION TAGGED '$date'

              Parameters

                  first_month_of_year = 1

              Fields

                  Year($1) As Year Tagged ('$numeric'),

                  Month($1) as Month Tagged ('$numeric'),

                  Date($1) as Date Tagged ('$date'),

                  Week($1) as Week Tagged ('$numeric'),

                  Weekday($1) as Weekday Tagged ('$numeric'),

                  DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric') ;

               

              And use one of this option to map existing data fields to the calendar (this will create the derived fields) :

              • Map specific fields by field name.
                • DERIVE FIELDS FROM FIELDS OrderDate,ShippingDate USING Calendar;
              • Map all fields with one or more specific field tags.
                • DERIVE FIELDS FROM EXPLICIT TAGS '$date' USING Calendar;
              • Map all fields that are tagged with one of the tags of the field definition ($date in the example above).
                • DERIVE FIELDS FROM IMPLICIT TAG USING Calendar;
              • Re: Date column is not loading as "Date & Time"
                Ahammad Shafi

                Hi All:

                 

                Many thanks for the replies.

                 

                Sorry that I failed to clarify what I want - I want to load the F1.PERIOD column as "Date & Time" so that I can calculate other time series like QTR, MAT and YTD.

                 

                 

                Hi Sebastien, Hi Jonathan:

                 

                I am trying the solutions you have suggested. On the other hand, please find the sample data and loading scripts.

                 

                SET ThousandSep=',';

                SET DecimalSep='.';

                SET MoneyThousandSep=',';

                SET MoneyDecimalSep='.';

                SET MoneyFormat='$#,##0.00;($#,##0.00)';

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

                SET DateFormat='YYYYMM';

                SET TimestampFormat='YYYYMM h: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';

                SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

                SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

                SET FirstWeekDay=6;

                SET BrokenWeeks=1;

                SET ReferenceDay=0;

                SET FirstMonthOfYear=1;

                SET CollationLocale='en-US';

                 

                 

                LOAD

                    CTY,

                    Cat_Code,

                    Category,

                    CRP,

                    CTY_DESCR,

                    FRM3,

                    INTPCK,

                    INTPRD,

                    INTREIM,

                    INTSIZE,

                    INTSTR,

                    MNF__OWN,

                    Ingredient,

                    Ingredient_COUNT,

                    NON_BIOCOMP,

                    PCK,

                    PCK_VOLUME,

                    SALT,

                    F1.PERIOD,

                    F1.UN,

                    F1.USD_MNF

                FROM [lib://SampleData/SampleData.xlsx]

                (ooxml, embedded labels, table is GERMANY_N7B0);

                 

                 

                 

                Ahammad Shafi

                  • Re: Date column is not loading as "Date & Time"
                    Sébastien Fatoux

                    I think you don't work my proposition because that woks well :

                    You can play with Calendar definition to add or remove fields

                     

                     

                    SET ThousandSep=',';

                    SET DecimalSep='.';

                    SET MoneyThousandSep=',';

                    SET MoneyDecimalSep='.';

                    SET MoneyFormat='$#,##0.00;($#,##0.00)';

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

                    SET DateFormat='YYYYMM';

                    SET TimestampFormat='YYYYMM h: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';

                    SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

                    SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

                    SET FirstWeekDay=6;

                    SET BrokenWeeks=1;

                    SET ReferenceDay=0;

                    SET FirstMonthOfYear=1;

                    SET CollationLocale='en-US';

                     

                    Calendar:

                    DECLARE FIELD DEFINITION TAGGED '$date'

                    Parameters

                        first_month_of_year = 1

                    Fields

                        Year($1) As Year Tagged ('$numeric'),

                        Month($1) as Month Tagged ('$numeric'),

                        Date($1) as Date Tagged ('$date'),

                        Week($1) as Week Tagged ('$numeric'),

                        Weekday($1) as Weekday Tagged ('$numeric'),

                        DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric') ;

                     

                    LOAD

                        CTY,

                        Cat_Code,

                        Category,

                        CRP,

                        CTY_DESCR,

                        FRM3,

                        INTPCK,

                        INTPRD,

                        INTREIM,

                        INTSIZE,

                        INTSTR,

                        MNF__OWN,

                        Ingredient,

                        Ingredient_COUNT,

                        NON_BIOCOMP,

                        PCK,

                        PCK_VOLUME,

                        SALT,

                        F1.PERIOD,

                        F1.UN,

                        F1.USD_MNF

                    FROM [lib://SampleData/SampleData.xlsx]

                    (ooxml, embedded labels, table is GERMANY_N7B0);

                     

                    DERIVE FIELDS FROM EXPLICIT TAGS '$date' USING Calendar;