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. !
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:
LOAD <fields>, timepoint, <fields>;
SQL SELECT <fields>, timepoint, <fields>from tableA;
SQL SELECT <fields>, timepoint, <fields> from tableB;
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.
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:
$(varMinDate) + Iterno()-1 As Num,
timestamp((($(#varMinDate) * 24) +(Iterno()-1))/24) as masterCalendarDate
Autogenerate(1) While ($(#varMinDate) * 24) +(Iterno()-1) <= $(#varMaxDate) * 24;
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.
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.
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);