452770_en-US

liliconcom

Full Outer Join- Timestamp not matching- adding more rows

 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?

AnalyticsData TableRe: Full Outer Join- Timestamp not matching- adding more rows

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

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

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())));