topic Re: Full Outer Join- Timestamp not matching- adding more rows in Discussions

Full Outer Join- Timestamp not matching- adding more rows

SymS — Sat, 10 Jun 2023 23:42:54 GMT

I need to match 6 files with data collected at different time stamps from different devices (all with 1 minute resolution in 2021).

(Note: I need a physically "joined" file for a different purpose, So, not doing Virtual Join)

I tried full outer join (including non-matches from "main table" and "with table"; Did not include "drop multiples" for either of the tables.

But it kept adding more rows even though there was a match. So I created a subset and attached here.

I created a minute Time stamp for 2021 in "TS-2021.jmp" with 525,600 rows

I am trying to match timestamps from "App ST-2021-Test.jmp" with 723 rows.

The result of the Full outer Join is in TS+AppST-2021-Test,jmp. 526,306 rows

My understanding is that the resultant file should have 525,600 rows and that I should find data populated only in the rows where timestamps match. But that is not the case.

It has matched 17 rows, but added extra rows even though there is a match. Not sure why. I tried some math functions on the timestamp- but that did not give me a satisfactory answer. Not sure what is going on? Any help?

Re: Full Outer Join- Timestamp not matching- adding more rows

dale_lehman — Thu, 20 Jan 2022 18:45:31 GMT

I can't tell what you are referring to. When I match excluding all non-matches, there are 17 rows that match in both files. If I include the non-matches from TS-2021, I get a total of 525,600 rows. If I include non-matches from both files, I get 526,306 rows. So, there are 17 dates that are in both files, 525,583 rows that only have 2PA TS dates and 706 that only have Timestamp dates. If you join including non matches for both and then select rows where each date column is missing, you will match those numbers. And those numbers all see like what you should get if there are 17 dates that indeed match.

Re: Full Outer Join- Timestamp not matching- adding more rows

dale_lehman — Thu, 20 Jan 2022 18:50:42 GMT

A bit more - I looked at the missing data pattern in your joined file and attached it - it matches what I found. The 526,306 rows consist of the 525,583 non matched rows from the TS-2021 file, the 706 non matched rows from the App ST-2021 file, and 17 matched rows, for the total 526,306 rows that are in your joined file.

Re: Full Outer Join- Timestamp not matching- adding more rows

jthi — Thu, 20 Jan 2022 19:39:34 GMT

At least APP ST-2021-Test timestamp has also seconds, not just minutes. You will have to remove those first, not just change format to something without seconds, because they are still there. One way to remove seconds is to deduct Second(Timestamp) from the timestamp.

Show(AsDate(Today() - Second(Today())));

Re: Full Outer Join- Timestamp not matching- adding more rows

SymS — Thu, 20 Jan 2022 20:08:18 GMT

I am still perplexed:

In the file "AppST-2021-Test.jmp" The first few lines (say 1-10- Jan 3rd 2021 3.57AM- Jan 3rd 4.06 AM in column(2PA TS) matches with

Lines 3118-3127 in file "TS-2021" -- Is It not?

Then why do we see "dots" against those values in the results file from lines 3824-3833

I thought that all values should be populated against these matching timestamps.

Is my understanding not right? What am I missing?

Re: Full Outer Join- Timestamp not matching- adding more rows

SymS — Thu, 20 Jan 2022 20:44:15 GMT

Thanks, I learned something new both in formatting and using other formulas. Removed seconds. After doing that I tried the Join.... It worked.

Re: Full Outer Join- Timestamp not matching- adding more rows

SymS — Thu, 20 Jan 2022 20:47:48 GMT

Thanks Dale for your efforts and introducing me to missing data pattern.

Re: Full Outer Join- Timestamp not matching- adding more rows

jthi — Thu, 20 Jan 2022 21:05:41 GMT

From this you can see that that there are seconds which are different from 00 (try changing format from column properties). Even thou it shows them as minutes, the seconds are in reality there as the values are in datetime (numeric value) which is "prettified" by formatting

And in TS-2021 quickly checking it seems like that the seconds are always 00.

Re: Full Outer Join- Timestamp not matching- adding more rows

SymS — Thu, 20 Jan 2022 21:55:36 GMT

I have a follow up Q for the same :I joined 2 tables (subtracting the seconds from time stamp) and from the result table I see the the row count has jumped from 525,600 (which is default for 1 min resolution for a year) to 525,929 rows. How do I find which rows have been added? In my thinking I expect the results table should have the default 525,600 rows no matter what, as I see all the other tables to be joined as a subset of this timestamp.

Re: Full Outer Join- Timestamp not matching- adding more rows

Jeff_Perkinson — Thu, 20 Jan 2022 22:13:39 GMT

In the Join dialog you'll see an option for a Match Flag.

That will add a column to your resulting table saying whether the row came from the Main table, With table, or Both.

Any that came from only the With didn't have a match in Main.