Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
[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;
Try this
[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;
if I understand your problem correctly , you want date to be populated based on join with kjobcode?
The problem is your join seems to be on 4 different field(as I understand from table)
description,event-qty,kjobcode,mach-id
so unless all field have same value, it will not populate. A way I can think of is Load kjobcode and extracted date separately and join with existing joined table(remove extracts from first load) or let it be separate(they will still associate)
I didn't go in detail at load
Doubling lines up.
Day Completed | description | description1 | elapse-time | event-qty1 | mach-id1 | End Date |
15 | Web Break | Print LAS | 293 | 13903 | 3PRT | 15/03/2019 |
15 | Web Break | Slit LAS | 293 | 2717.471 | 4SL | 15/03/2019 |
Sorry should have included mach-id
Day Completed | description | description1 | elapse-time | event-qty1 | mach-id1 | End Date | mach-id |
15 | Web Break | Print LAS | 293 | 13903 | 3PRT | 15/03/2019 | 4SL-TTNS |
15 | Web Break | Slit LAS | 293 | 2717.471 | 4SL | 15/03/2019 | 4SL-TTNS |
Okay i have changed the approach on this one. I now have a table output as below.
The question is how do i either via load script or expression only show the max run number which maybe different for each kjobcode. So in above case max Run number is 76 and the only line showing should be line 2 for this Kjobcode. It must be by kjobcode.
Script Below.
[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 > 1;
@bnelson111 wrote:The question is how do i either via load script or expression only show the max run number which maybe different for each kjobcode. So in above case max Run number is 76 and the only line showing should be line 2 for this Kjobcode. It must be by kjobcode.
Front end is fairly simple. Create a chart with kjobcode as dimension and use Max(RunNumber) as your expression