Table Join Troubleshooting

[#1]

I frequently run into issues when attempting to join multiple data tables, and I am not sure what I am missing. 

Most commonly, I am looking to join tables by matching a date. Sometimes this works no problem, sometimes I struggle significantly and have to try a large number of different options within the join dialogue. 

Typically, I have Table A with thousands of rows logging machine data. Each row has a DateTime stamp. I create a Date column using the substr() command on the first 10 characters of the DateTime String, and then have my date column in YYYY-MM-DD format. Each date may be associated with thousands of individual rows.

Table B is a list of Product by Day. 

I attempt to join Table A with Table B, such that I have a product tag labeled on each individual row of Table A. (ex. if Table A has 150,000 rows representing 100 days worth of data, Table B has 100 rows of product by date, Table C is 150,000 rows of the original data, with a product label taken from Table B.) I've done this successfully many times in the past, but occasionally run into problems replicating it, and I can't figure out why. 

Some things I've tried already: 

-Verify that I am not attempting to match YYYY-MM-DD data with YYYY-MM-DD H:M:S data

-Ensured the formulas used to extract the date data from the DateTime columns match exactly 

-Copied the date column output data into a new column on both tables, and then matching that to avoid any possible issues from the formula 

-Have tried different data/modeling types to represent the string 

All of these have given me tables with no successful joins. Any idea on what I may be missing here, or general "best practice tips" for Table Joins? I have read the JMP documentation, and have had success using Table Joins in the past, but am looking to better understand what may be going on here. Thanks 

[#2]

I have gotten it to work by copy and pasting each Date column into excel, and then recopy and pasting it into JMP for both tables. However, I'd still like to understand what's going on here causing this to be an issue. 

[#3]

Hi,

Do you have an example of a join where you cannot seem to obtain the desired result? If so, if you would attach the following, I believe we'll have a better idea of exactly what is going on and how to advise:

• table A and table B (sanitized, of course, in the event that either contains sensitive data). These can be partial... no need to upload massive tables.
• the (bad) table C, with its "Source" script intact
• at least a few rows of a successful table "C"... at least, what the successful join WOULD look like. Or, in lieu of this, an explanation of what is wrong with joined table C (i.e., "row 21 is incorrect because..." or "there should be a row with these elements, but there is not...")

There are MANY ways joins can go sideways... this will help us to understand your particular case.

Cheers,

Brady

[#4]

Time stamp data can cause issues because the values can contain different times during a given day, but if the formula being used to display the data is a "Date Only" display, with the time component not being displayed, the user can think there is a match, but the actual numeric values are different.  

If you convert to a character string, the making sure the format used to convert to is critical, as you suggest.  The other item that I find that is important, are potential proceeding or following blank spaces.  So what I do when converting to a character string is to use the Trim() function to trim off those potential blank spaces.

[#5]

Apologies all for not following up on this thread at the time of posting. 

I had been using the SubStr command to extract a date from a datetime string for my date column. I switched both of my date column formulas to the following:

Abbrev Date( Num( :DateTime ), "YYYY-MM-DD" )

and it fixed my problem. I also determined that the Update function probably made more sense for my need.