Date column is not loading as "Date & Time"

[ahammadshafi]

Dear Experts:

Please note, in the input excel file the date column is in "YYYYMM" format. 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. .

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.

Ahammad Shafi

[Anonymous]

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'  

[jonathandienst]

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.

[sfatoux72]

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;

[deepakqlikview_123]

Not able to get what exactly you want can you please share more details.use date function for formatting the date.

Thanks

[ahammadshafi]

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

[sfatoux72]

When you go to "Data Model Viewer" and you select your field "F1.PERIOD" with Preview pane open, do see $Date in tags?

[sfatoux72]

It seems that it will be automatic in Qlik Sense 2.2

[sfatoux72 · Accepted solution]

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;