7 Replies Latest reply: Feb 4, 2016 7:58 PM by jagan mohan rao appala RSS

    Qlik Sense - Duplicate timepoints

    jonathan sica

      I have two tables, each with a timepoint column called masterCalendarDate. If I load them one at a time into my qvf it loads as expected - one timepoint per hour.  If I load both tables together then it gives duplicate timepoints for masterCalendarDate at what seems like random places.

       

      Side note - the duplicate timepoints are treated as different values.  If you load masterCalendarDate into a UI table and click one of the timepoints, the other timepoint does not get selected.  Also, if I convert the timepoints to a number they look to be exactly the same. !

        • Re: Qlik Sense - Duplicate timepoints
          Dave Vernon

          Hi Jonathan,

           

          Could you give a bit more more information when you say:

          "If I load them one at a time into my qvf it loads as expected" vs "If I load both tables together"

           

          Do you mean by 'one at a time', your loadscript has a specific JOIN statement between the LOAD of table A and table B, while the both together method looks like this:

           

          resultData:

          LOAD <fields>, timepoint, <fields>;

          SQL SELECT <fields>, timepoint, <fields>from tableA;

          SQL SELECT <fields>, timepoint, <fields> from tableB;

           

          Dave

            • Re: Qlik Sense - Duplicate timepoints
              jonathan sica

              If I load only one table into the QVF then I do in fact have a list of unique timepoints.  If I load both tables into the QVF I get duplicate timepoint entries randomly.

               

              The code below is more or less what each table load into the QVF looks like:

               

              TABLE 1

              MasterCalendar:

              LOAD 

                             $(varMinDate) + Iterno()-1 As Num,

                             timestamp((($(#varMinDate) * 24) +(Iterno()-1))/24) as masterCalendarDate

                             Autogenerate(1) While ($(#varMinDate) * 24) +(Iterno()-1) <= $(#varMaxDate) * 24;

               

              TABLE 2

              hourlyData:

              LOAD * FROM C:\hourlyDate.qvd (qvd);

               

              The one thing I am about to try is explicitly load name each column within the TABLE 2 load and cast the datetime columns as timestamps like I do within the MasterCalendar.  I didn't think this needed to be done because it is not done elsewhere with other table load sections.

            • Re: Qlik Sense - Duplicate timepoints
              Marcus Sommer

              The reason is probably that you mixed dates and timestamps. A good way to handle it is to split the timestamp into a date and a time per:

               

              date(floor(YourTimestampField)) as Date,

              time(frac(YourTimestampField)) as Time

               

              and to connect these with a master-calendar and a master-timetable.

               

              Here you will find various examples and more informations about How to use - Master-Calendar and Date-Values.

               

              - Marcus