5 Replies Latest reply: Nov 12, 2015 10:37 AM by Jonathan Poole RSS

    Creating a master dimension - datetime to date format

      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

        • Re: Creating a master dimension - datetime to date format
          Jonathan Poole

          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 !


            • Re: Creating a master dimension - datetime to date format

              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.

                • Re: Creating a master dimension - datetime to date format
                  Jonathan Poole

                  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.