Joining two data tables and one row is not matching, but unsure why?

bjohnson

I am working on joining two JMP tables.

All data is joining correctly, except one row. I have checked to make sure they are copied the same and have the same characteristics, and used the match flags to confirm which table is causing the error. Although I have determined which table is the problem, I am still not sure why?

 

Table 1:

 

 

New SQL Query(
 

 

QueryName( "1. Curd xfer with time offsets" ),
 
CustomSQL(
"SET NOCOUNT ON
 
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '20170115 00:00:00.000'
SET @EndDate = '20170122 00:00:00.000'
SET NOCOUNT OFF
SELECT * FROM (
SELECT History.TagName, DateTime, Value, vValue, StartDateTime
FROM History
WHERE History.TagName IN ('DAL.CP5.CHEESE.CVSTATUS.CURD_XFER_VAT')
AND wwRetrievalMode = 'Cyclic'
AND wwResolution = 30000
AND wwVersion = 'Latest'
AND DateTime >= @StartDate
AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate"
)
) << Run;
 
　
While (try(dt1<<get name) != "1. Curd xfer with time offsets",
Wait(2);
dt1 = Current Data Table()
 
);
dt1 = Current Data Table();
 
　
//Define Column Properties
　
Column(dt1, "vValue") << Data Type (Numeric) << Format("Best", 10) << Modeling Type("Nominal");
Column(dt1, "DateTime") << Data Type (Numeric) << Format( "m/d/y h:m:s", 23, 0 ) << Modeling Type("Continuous");
Column(dt1, "StartDateTime") << Data Type (Numeric) << Format( "m/d/y h:m:s", 23, 0 ) << Modeling Type("Continuous");
 
　
//Add New Formula Columns
　
New Column( "DAL.CP5.CHEESE.CCB01.LT_01684_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
Formula( :DateTime + :0:00:44:30 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.LT_01685_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
Formula( :DateTime + :0:00:47:00 ),
 
);
 
New Column( "DAL.CP5.CHEESE.CCB01.LT_01686_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
Formula( :DateTime + :0:00:50:30 ),
 
);
 
New Column( "DAL.CP5.CHEESE.CCB01.LT_01687_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
Formula( :DateTime + :0:00:53:00 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.LT_01688_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
Formula( :DateTime + :0:00:56:30 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.TIC_01510_PV",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
Formula( :DateTime + :0:00:12:30 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.FIC_01410_PV",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
Formula( :DateTime + :0:00:12:30 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CVSTATUS.CURD_XFER_VAT",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
 
Formula( :DateTime ),
);
　
New Column( "DAL.CP5.CHEESE.CCB01.LT_01662_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:00:06:00 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.LT_01672_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:00:26:45 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.TE_01590",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:01:05:00 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CVSTATUS.CURD_XFER_RECIPE",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
 
Formula( :DateTime ),
);
　
New Column( "DAL.CP5.CHEESE.CCB01.LT_01652_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:00:01:30 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.LT_01662_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:00:06:00 ),
 
);
　
　
New Column( "DAL.CP5.CHEESE.CCB01.LT_01672_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:00:26:45 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.LT_01688_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:00:56:30 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.CTK02010.MCCB_02310A_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:00:44:30 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.CTK02020.MCCB_02320A_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:00:47:00 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.CTK02030.MCCB_02330A_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:00:50:30 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.CTK02040.MCCB_02340A_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:00:53:00 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.CTK02050.MCCB_02350A_RAW",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:00:56:30 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCT01.CURD_LT",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:01:10:00 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.PRGM_BELT_STOP_CTR1_ACC",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:00:56:30 ),
 
);
　
New Column( "DAL.CP5.CHEESE.CCB01.PRGM_BELT_STOP_ACC",
 
Numeric,
"Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Formula( :DateTime + :0:00:56:30 ),
 
);
　
//Stack Values
　
dt2 = Data Table( "1. Curd xfer with time offsets" ) <<
 
Stack(
columns(
:DAL.CP5.CHEESE.CCB01.LT_01684_RAW,
:DAL.CP5.CHEESE.CCB01.LT_01685_RAW,
:DAL.CP5.CHEESE.CCB01.LT_01686_RAW,
:DAL.CP5.CHEESE.CCB01.LT_01687_RAW,
:DAL.CP5.CHEESE.CCB01.LT_01688_RAW,
:DAL.CP5.CHEESE.CCB01.TIC_01510_PV,
:DAL.CP5.CHEESE.CCB01.FIC_01410_PV,
:DAL.CP5.CHEESE.CVSTATUS.CURD_XFER_VAT,
:DAL.CP5.CHEESE.CCB01.LT_01662_RAW,
:DAL.CP5.CHEESE.CCB01.LT_01672_RAW,
:DAL.CP5.CHEESE.CCB01.TE_01590,
:DAL.CP5.CHEESE.CVSTATUS.CURD_XFER_RECIPE,
:DAL.CP5.CHEESE.CCB01.LT_01652_RAW,
:DAL.CP5.CHEESE.CCB01.LT_01672_RAW,
:DAL.CP5.CHEESE.CCB01.CTK02010.MCCB_02310A_RAW,
:DAL.CP5.CHEESE.CCB01.CTK02020.MCCB_02320A_RAW,
:DAL.CP5.CHEESE.CCB01.CTK02030.MCCB_02330A_RAW,
:DAL.CP5.CHEESE.CCB01.CTK02040.MCCB_02340A_RAW,
:DAL.CP5.CHEESE.CCB01.CTK02050.MCCB_02350A_RAW,
:DAL.CP5.CHEESE.CCT01.CURD_LT,
:DAL.CP5.CHEESE.CCB01.PRGM_BELT_STOP_CTR1_ACC,
:DAL.CP5.CHEESE.CCB01.PRGM_BELT_STOP_ACC,
),
Source Label Column( "Label" ),
Stacked Data Column( "Data" ),
Name( "Non-stacked columns" )(Keep( :DateTime, :Value )),
Output Table( "2. Stacked tags with vat and time" ),
 
);
　
Close(dt1);

 

Data Table 2:

 

 

New SQL Query(
 

 
QueryName( "3. Tags with Values" ),
 
CustomSQL(
"SET NOCOUNT ON
 
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '20170115 00:00:00.000'
SET @EndDate = '20170122 00:00:00.000'
SET NOCOUNT OFF
SELECT * FROM (
SELECT History.TagName, DateTime, Value, vValue, StartDateTime
FROM History
WHERE History.TagName IN
('DAL.CP5.CHEESE.CCB01.LT_01684_RAW', 'DAL.CP5.CHEESE.CCB01.LT_01685_RAW', 'DAL.CP5.CHEESE.CCB01.LT_01686_RAW', 'DAL.CP5.CHEESE.CCB01.LT_01687_RAW', 'DAL.CP5.CHEESE.CCB01.LT_01688_RAW', 'DAL.CP5.CHEESE.CCB01.TIC_01510_PV', 'DAL.CP5.CHEESE.CCB01.FIC_01410_PV', 'DAL.CP5.CHEESE.CVSTATUS.CURD_XFER_VAT', 'DAL.CP5.CHEESE.CCB01.LT_01662_RAW','DAL.CP5.CHEESE.CCB01.TE_01590', 'DAL.CP5.CHEESE.CVSTATUS.CURD_XFER_RECIPE', 'DAL.CP5.CHEESE.CCB01.LT_01652_RAW', 'DAL.CP5.CHEESE.CCB01.LT_01662_RAW', 'DAL.CP5.CHEESE.CCB01.LT_01686_RAW', 'DAL.CP5.CHEESE.CCB01.CTK02010.MCCB_02310A_RAW', 'DAL.CP5.CHEESE.CCB01.CTK02020.MCCB_02320A_RAW', 'DAL.CP5.CHEESE.CCB01.CTK02030.MCCB_02330A_RAW', 'DAL.CP5.CHEESE.CCB01.CTK02040.MCCB_02340A_RAW', 'DAL.CP5.CHEESE.CCB01.CTK02050.MCCB_02350A_RAW', 'DAL.CP5.CHEESE.CCT01.CURD_LT', 'DAL.CP5.CHEESE.CCB01.PRGM_BELT_STOP_CTR1_ACC', 'DAL.CP5.CHEESE.CCB01.PRGM_BELT_STOP_ACC', 'DAL.CP5.CHEESE.CCB01.LT_01672_RAW')
AND wwRetrievalMode = 'Cyclic'
AND wwResolution = 30000
AND wwVersion = 'Latest'
AND DateTime >= @StartDate
AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate"
)
) << Run;
 
　
While (try(dt3<<get name) != "3. Tags with Values",
Wait(2);
dt3 = Current Data Table();
 
);
　
//Define Column Properties
　
Column(dt3, "vValue") << Data Type (Numeric) << Format("Best", 10) << Modeling Type("Nominal");
Column(dt3, "DateTime") << Data Type (Numeric) << Format( "m/d/y h:m:s", 23, 0 ) << Modeling Type("Continuous");
Column(dt3, "StartDateTime") << Data Type (Numeric) << Format( "m/d/y h:m:s", 23, 0 ) << Modeling Type("Continuous");
 
　Joined Script:
Data Table( "2. Stacked tags with vat and time" ) <<
 
Join(
With( Data Table( "3. Tags with Values" ) ),
 
By Matching Columns( :Label = :TagName, :Data = :DateTime ),
Drop multiples( 0, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 ),
Output Table( "4. Joined curd xfer and offsets with tags and values" )
 
)

 

The data that is not transferring is for DAL.CP5.CHEESE.CCB01.LT_01672_RAW.

 

Can anyone assist?

 

Thank you!

 

stan_koprowski

Hi,

Looking at your scripts quickly I see you have one column defined twice (DAL.CP5.CHEESE.CCB01.LT_01672_RAW)

JMP will append a numeric suffix to the repeated name of the column.  In your case, "DAL.CP5.CHEESE.CCB01.LT_01672_RAW 2"

So your stacked data table is most likely not producing the output you expect. 

If you could provide samples of each of the three data tables we can help narrow the issue down further.

 

--Stan

 

 

stack probably not producing what you are expecting

 

Notice you create the same column twice