53570_en-US

liliconcom

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

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=""></GET>

 

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=""></GET>

 

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

 

Can anyone assist?

 

Thank you!

 

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

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