Joined a table but no time stamp in joined table.

[bnelson111]

Good morning, i have a interesting question in relation to joining a two table together with a common data field in this case its the kjobcode. Everything works okay however i don't have a Month Completed or Year Completed from the second table see below, i have provide a line from each original table. First has a end date which i then make a Month and Year for analysis. Second line is the joined table which has no date? How do i take the date from the 1st table?

Day Completed	description	End Date	event-qty	event-type	kjobcode	mach-id	Month Completed	Year Completed
15	Running	15/03/2019	2092	G	211779	3PRT-NOV	Mar	2019
	Print LAS		13903		211779	3PRT

Also below is the Load Script, i hope this makes sense? Thanks in advance. i have also included a pivot table result.

Pivot Graph

[SFDC DATA]:

LOAD

kjobcode,

"mach-id",
"stat-code",
"op-code",
"start-time",
Interval#( "elapse-time",'s') as "Seconds",

"elapse-time",

"event-type",

date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YYYY') as "End Date",
day(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Day Completed",
Month(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Month Completed",
year(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Year Completed",
date(Date(Date('1990-01-01 00:00:00.000') +Num#("start-time"/86400)),'DD/MM/YYYY') as "Event Logged Time",
If(Frac(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YYYY')) >= MakeTime(8) and Frac(date(Date(Date('1990-01-01 00:00:00.000')+Num#("end-time"/86400)),'DD/MM/YYYY')) <= MakeTime(20), 'Day 08:00 TO 20:00', 'Night 20:00 TO 08:00') as B,

amended,

"group-code",
"seq-num",
"sub-type",

"event-qty";

SQL SELECT

kjobcode,
"end-time",

"mach-id",
"stat-code",
"op-code",
"start-time",
"elapse-time",

"event-type",

amended,

"group-code",
"seq-num",
"sub-type",

"event-qty"

FROM PUB.sfeventcds

WHERE "start-time" > 915270818;

// $(varMaxDate)use 789044901 757500265 $(varMaxDate) was 851346857, used > 865121989, < 882744802

left join
LOAD "stat-code",
description,
"type",
cost,
kco,
movement,
indirect,
"trans-cost",
"sundry-code",
"restrict-flag",
"sub-type",
PlantCode;
SQL SELECT *
FROM PUB.sfstatcode;
//-------- End Multiple Select Statements ------

ODBC CONNECT32 TO visionu (XUserId is aSPAHaRP, XPassword is aKRRBaNV);

join

[LAS]:

LOAD JobCode as kjobcode,

if(StepNum='1150', 'Print LAS' , if (StepNum='1250','Slit LAS' )) as description,
if(StepNum='1150', '3PRT',if(StepNum='1250', '4SL' ) ) as "mach-id",

UnrecScrapQty as [event-qty];

SQL SELECT
JobCode,
StepNum,
UnrecScrapQty

FROM PUB."PV_JobStepVar"
Where UnrecScrapQty > 1;

[bnelson111]

That looks brilliant , thank you

[bnelson111]

One last question the links have now created a $Syn 1 table, whats the easiest way to join them i had been using a join but script now has a right join at end and previous table appears to be joined.

Load Script.


[SFDC DATA]:


LOAD



kjobcode,

"mach-id",
"stat-code",
"op-code",
"start-time",
Interval#( "elapse-time",'s') as "Seconds",

"elapse-time",

"event-type",

date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YYYY') as "End Date",
day(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Day Completed",
Month(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Month Completed",
year(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Year Completed",
date(Date(Date('1990-01-01 00:00:00.000') +Num#("start-time"/86400)),'DD/MM/YYYY') as "Event Logged Time",
If(Frac(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YYYY')) >= MakeTime(8) and Frac(date(Date(Date('1990-01-01 00:00:00.000')+Num#("end-time"/86400)),'DD/MM/YYYY')) <= MakeTime(20), 'Day 08:00 TO 20:00', 'Night 20:00 TO 08:00') as B,

amended,

"group-code",
"seq-num",
"sub-type",

"event-qty";

SQL SELECT

kjobcode,
"end-time",

"mach-id",
"stat-code",
"op-code",
"start-time",
"elapse-time",

"event-type",

amended,

"group-code",
"seq-num",
"sub-type",

"event-qty"


FROM PUB.sfeventcds

WHERE "start-time" > 915270818;


left join
LOAD "stat-code",
description,
"type",
cost,
kco,
movement,
indirect,
"trans-cost",
"sundry-code",
"restrict-flag",
"sub-type",
PlantCode;
SQL SELECT *
FROM PUB.sfstatcode;
//-------- End Multiple Select Statements ------

ODBC CONNECT32 TO visionu (XUserId is aSPAHaRP, XPassword is aKRRBaNV);


[LAS]:
LOAD JobCode as kjobcode,
if(StepNum='1150', 'Print LAS' , if (StepNum='1250','Slit LAS' )) as description,
if(StepNum='1150', '3PRT',if(StepNum='1250', '4SL' ) ) as [mach-id],
RunNumber,
UnrecScrapQty as [event-qty];
SQL SELECT
JobCode,
StepNum,
RunNumber,
UnrecScrapQty
FROM PUB."PV_JobStepVar"
WHERE UnrecScrapQty > 0;

Right Join (LAS)
LOAD kjobcode,
Max(RunNumber) as RunNumber
Resident LAS
Group By kjobcode;

[sunny_talwar]

I am not sure I follow your question. 

[bnelson111]

Sorry its breaking the table. 

[bnelson111]

Sorry its breaking the table. 

[sunny_talwar]

What would you want it to do?

[bnelson111]

Want it all in one table so the solution you give to table [LAS] and after group then join into the first table [SFDC DATA] but all of table [SFDC DATA] data.

[bnelson111]

Want it all in one table so the solution you give to table [LAS] and after group then join into the first table [SFDC DATA] but all of table [SFDC DATA] data.