Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ahammadshafi
Contributor

Date column is not loading as "Date & Time"

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

Tags (2)
1 Solution

Accepted Solutions
sfatoux72
Valued Contributor

Re: Date column is not loading as "Date & Time"

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;

8 Replies
balrajahlawat
Esteemed Contributor

Re: Date column is not loading as "Date & Time"

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'  

MVP
MVP

Re: Date column is not loading as "Date & Time"

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
Valued Contributor

Re: Date column is not loading as "Date & Time"

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;
deepaktibhe
Valued Contributor

Re: Date column is not loading as "Date & Time"

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

Thanks

ahammadshafi
Contributor

Re: Date column is not loading as "Date & Time"

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
Valued Contributor

Re: Date column is not loading as "Date & Time"

When you go to "Data Model Viewer" and you select your field "F1.PERIOD" with Preview pane open, do see $Date in tags?2016-02-15 10_16_18-Data model viewer - Qlik Sense.png

sfatoux72
Valued Contributor

Re: Date column is not loading as "Date & Time"

It seems that it will be automatic in Qlik Sense 2.2

sfatoux72
Valued Contributor

Re: Date column is not loading as "Date & Time"

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;

Community Browser