Choose Language Hide Translation Bar
Highlighted
Filipbombeke
Level I

Join tables

I want to join to two tables by matching columns (timestamp) with include non-matches for the onepager_HPPO table. The data from second table onepager HPPO_adddata_ONSTS is only present until 08/08/08 10:00 in the joined tables although it should be until end of 2019. The format of two timestamps is identical. What cause this issue or how can it be resolved?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Join tables

The issue is, that in data table ONEPAGER HIPPO_adddata_ONSTS, on 08/08/2018 at 11AM the input data changes.  At 10AM the value is

08/08/2018 10:00:00.000 AM

at 11AM it changes to

08/08/2018 10:59:59.999 AM

eleven.PNG

If you run a little script that rounds all of the values to whole numbers, and then join the data, that should fix the issue.

Jim

View solution in original post

3 REPLIES 3
Highlighted
Thierry_S
Level VI

Re: Join tables

Hi Filip,

It looks like there might be a bug: I did reproduce your problem even when I forced the date stamp to be in numerical format. The only work around I could find, which is rather ugly, is to extract the HOUR, DAY, MONTH, and YEAR in separate columns for each table and joined based on all 4 values.
Maybe somebody else will have more insight.
Best,
TS
Thierry R. Sornasse
Highlighted
txnelson
Super User

Re: Join tables

The issue is, that in data table ONEPAGER HIPPO_adddata_ONSTS, on 08/08/2018 at 11AM the input data changes.  At 10AM the value is

08/08/2018 10:00:00.000 AM

at 11AM it changes to

08/08/2018 10:59:59.999 AM

eleven.PNG

If you run a little script that rounds all of the values to whole numbers, and then join the data, that should fix the issue.

Jim

View solution in original post

Highlighted
Filipbombeke
Level I

Re: Join tables

Indeed - I did not noticed this. Rounding the timestamps solved the issue. Thanks!

Article Labels