Qlik Sense - Duplicate timepoints

[#1]

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. !

[#2]

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

[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

[#4]

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.

[#5]

UPDATE: Explicitly listing the columns when loading TABLE 2 and casting the datetime masterCalendarDate as a timepoint did not work.

[#6]

All of my timepoints are full datetimes at hourly intervals.  I do have a few rows within the table having > 0 minutes but those are not the rows I am having issues with.  It does seem to be every 3rd hour is a duplicate but I am not sure if that is just coincidence.

[marcus_sommer]

Honestly I didn't understand your master-calendar. Like above mentioned I would consider to use two fields date and time by using a master-calendar and a master-timetable. If you create a mixed master-table it should look more like this:

let varMinDate = 42200;

let varMaxDate = 42300;

Load Date, maketime(iterno()-1) as Time, timestamp(Date + maketime(Iterno()-1)) as TimeStamp while iterno()-1 < 24;

Load date($(varMinDate) + recno() - 1, 'DD.MM.YYYY') as Date autogenerate $(varMaxDate)- $(varMinDate);

- Marcus

[jagan]

Hi,

Check this link for sample script for generating Master Calendar

Master Calendar Generation Script

Regards,

Jagan.