12 Replies Latest reply: Feb 6, 2014 1:11 PM by Chuck Cadman RSS

    Best way to have Date fields ?

    Frédéric Villemin

      Hi,

       

      i'm trying to find the best way to have fast and useful date fields.

       

      My script contains only one SQL query which take 88 fields for 500 000 lines

       

       

      SQL SELECT ID,

          USERID,

          SITEID,

      ... (84 other fields)

           CREATION_DATE;

       

      The Creation_date is in a date format but i would like to make listboxes and dimensions with this date in several other forms : week / month / day / quarter

       

      What is the best way to do that ?

       

      1 - adding conversion in the query : to_char(CREATION_DATE, 'yyyy/mm') as month

      It will be really fast in Qlikview but will need 500000*7 chars of network transfer to get the data from database. If we think that there are several dates in my query and i want 4 conversions for each one, it will make a longer and bigger query transfer.

       

      2 - adding calculated fields in Qlikview : really slow. I have tried to make a listbox with year(CREATION_DATE) and it's really really slow. I can't keep that solution.

       

      3 - is it possible to add in the script the creation of a qlikview "table" in which i would declare for each ID the different conversion of CREATION_DATE. This way, Qlikview would only calculate all conversion once at script reloading and it would be really fast without too much network.

       

      Can i make solution 3 and how ?  or do you have any faster solution ?

       

      Thanks

        • Best way to have Date fields ?
          Stefan Wühl

          You can make use of any Qv date and time function (please look in the help for full details) in the LOAD part of your script.

           

          So you could use something like

           

          LOAD

          ID,

          SITEID,

          (84 other fields)

          CREATION_DATE,

          month(CREATION_DATE),

          year(CREATION_DATE),

          ... (more date functions)

          ;

          SQL SELECT ..

          from Table;

           

          You might also want to consider to create a master calendar and linking this calendar to your table using CREATION_DATE. Search for master calendar here in the forum, there a lots of examples / explanations.

           

          Regards,

          Stefan

          • Re: Best way to have Date fields ?
            Jonathan Shaltz

            I had the same issue, and found that master calendars get tricky when your SQL source has multiple date fields.  After a bit of hair pulling, I worked out a script which scans your SQL for date field and loads the master calendar once for each date field, prefixing the fields to avoid name collisions (the code is mostly from Witherspoon, I just adjusted it slightly).  I think this works pretty well; I can add a new date field to the SQL view which is used to populate my "Raw Dates.qvd" file, and all downstream reports will pick up the new field without me having to lift a finger.

             

            The thread is here: http://community.qlik.com/message/168562, and attached is the script.  A QVW which loads from one or more fact table QVDs, and then uses this script to create date tables, looks like this (after the "SET format" boilerplate):

             

            LOAD * FROM "\\SQLBI001\QlikView Documents\Raw Data\Raw Stores.qvd" (qvd);
            LOAD * FROM "\\SQLBI001\QlikView Documents\Raw Data\Raw Orders.qvd" (qvd);
            
            $(Include=Load Dates.qvs);
            

             

            Very compact, and very simple, which is helpful if you have non-technical users building reports.