Creating a master dimension - datetime to date format

[#1]

Hello everyone,

Firstly, please accept my apologies if this is something very straight forward. I am extremely new to Qlik Sense and currently working my way through the supporting videos whilst looking at some real data and applying the learnings as I go.

I am looking at some data where I have a field called Week Commencing. The format of this field is currently dd/mm/yyyy hh:mm:ss although the time aspect looks to always be set to 00:00:00.

I need to convert this field so that it is just the date represented and is therefore only dd/mm/yyyy. I was looking at using ToDate but haven't managed to get this to work.

Can anyone please advise how I can do this through creating as a master item. I have read my data in from an MS database and the script is therefore locked.

I appreciate any advice.

Many thanks

Claire

[JonnyPoole]

Hi Claire,  i would suggest doing this in the LOAD EDITOR so that you don't load excess granularity in the dashboard. Whether you do the following in the load editor or the master item dimension definition , the syntax is the same.

Lets say your datetime field is   [DateTime].

Just use:

Date(floor([DateTime]) , 'DD/MM/YYYY') 

In the script you would add an alias:

Date(floor([DateTime]) , 'DD/MM/YYYY')  as Date,

the explanation: 

- qlik interprets all dates as numbers. a whole number is a day and the decimal is the time portion

- the floor function will round down the number to a whole number effectively giving you a date without any time context

-then use the date() function to format the number in the date format you want.

gotchas:

- sometimes qlik sense can't interpret your raw data field as a date or date time without help

- to help it out, provide the format of the date that you expect using the date#() function.   Date#() is a date reading interpretation function /   Date() is a date display function.

so if your date times are in the database as  DD/MM/YYYY hh:mm:ss format then you can use this:

Date#([DateTime],'DD/MM/YYYY hh:mm:ss')

keep in mind that if your hours are stored  as   1:00:00  you would use  h:mm:ss  . If they are stored as 01:00:00 then you use hh:mm:ss.  Its very granular

Put it all together and you have something like this where you are reading date in a specific format, dropping the time, and display in a specific format all at once:

Date(floor(Date#([DateTime],'DD/MM/YYYY hh:mm:ss')) , 'DD/MM/YYYY')  as Date,

sometimes you have milliseconds (fff) and AM/PM to deal with too.  Here are the masks for those as well

Date#([DateTime],'DD/MM/YYYY hh:mm:ss.fff TT')

good luck !

[#3]

Thank you Jonathan, that's worked a treat!

I created my field in the Master Items. How should I go about creating it within the Load Editor? In the Load Editor I have 'Main' and then my 'Auto-generated section' which is locked. Should I unlock that and add at the end of the code?? I'm nervous about unlocking it........

Thank you as well for the explanation. Understanding how Qlik works compared with other applications I've used is hard because you know the formula you want but not how Qlik wants you to write it. I'm struggling a lot in this area at the moment as I look to get to grips with it so you may see me popping up with other questions on how to write some formulas.

[JonnyPoole]

You can unlock it but it will disable the 'add data' and 'data manager' features for the app moving forward.

You could also add a new section to the load editor to come after the locked section. Hit the '+' icon in the data load editor ( i think its bottom left by memory) to add a new section and drag it to the bottom of the sections on the left side.

In the new section, create a new table called 'Dates' which will load all your dates and bucket them.

Dates:

Load distinct

     DateField,

     month(Datefield) as Month,

     year(Datefield) as Year

resident <existingdatatable>;

for <existingdatatable>  , go to the 'data model viewer' and look at the name of your existing table, and use that name without '<>' characters after 'resident' .  Its called a resident load of data that you already loaded in. For this purposes you are creating a calendar table with some pre-built functions. This way you populate all the potential date buckets in advance.

[#5]

Hi Jonathan,

Sorry for the late reply, there's been a lot going on at work recently.

I've created a Variables table in the load editor where I would like to try and create many of the additional variables I need and have tried using the week commencing date one as an example using your script example.

When I debug the code, I get a red line in row 2 so I can't be scripting correctly. I've used SAS and SQL in the past so naturally I think along those coding principles and languages. Can you tell me where I'm going wrong?

Dates:

Load Distinct
[Week Commencing],
Date(floor([Week Commencing]) , 'DD/MM/YYYY') as "Week Comm"

Resident tblreporting;

[JonnyPoole]

hi - i cannot see a syntax error in the code you posted. Would you mine adding a screenprint with the red line as you mentioned ?