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;
Sunny, tried that but see below expression.
sum({<RunNumber={'=Max(RunNumber)'}>}[event-qty])
its resulting in
In this case when two different Jobs have been selected it should have only show on the output table picture, rows 1,2,3. Row 4 should be dropped as the kjobcode 212258 has a runnumber 76 in this case i want only to see info relating to 76 runnumber but on kjobcode 211779 both lines on output table are correct as they have a maximum runmuber for that kjobcode off 75 therefore any data with a 75 should be shown, so in summary each Kjobcode can have a different max runnumber and thats only what i want to show for that kjobcode.
Try this
FirstSortedValue(Aggr(Sum([event-qty]), RunNumber), -RunNumber)
Its has unfortunately dropped the Slit LAS from kjobcode 211779, well it actually has subtracted it from the Print LAS
I am not sure I follow...
Sorry, i'm not explaining very well. I have basically data which is captured at different run times into a database the runtimes are represented by a Runnumber. In order for me to get accurate data i must look at each line and group by kjobcode select the largest runnumber. The problem occurs when under a kjobcode number there are different steps in this case mach-id, so i need to then have the largest runnumber by kjobcode by mach-id then sum the event-qty. i have tried to group in load statement but not working see 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";
Nozeros:
noconcatenate LOAD
kjobcode,
description,
[mach-id],
MAX(RunNumber) as Total_TRX,
[event-qty]
Resident [LAS]
where [event-qty] > 0 Group By kjobcode Order By kjobcode;
Left Join
LOAD
kjobcode,
escription,
[mach-id],
Total_TRX
Resident LAS;
Drop Table LAS;
1st are you looking to do it in the script or front end? If you want this in the back end, I would suggest you to provide 10-15 rows of data and the output you are looking to get from it.
For front end, you can try this
FirstSortedValue( Aggr( Sum([event-qty]) , RunNumber, kjobcode, description), -Aggr( RunNumber , RunNumber, kjobcode, description) )
Thank you for your help and patience.
Okay i have attached a excel file with the data table output (Straight table on dim or expressions) outputted with the following stripped back Load Statement.
The rows marked in yellow are the results i wish to have. (in excel sheet, i have sorted by kjobcode just to see expectations)
[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 ;
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 description, kjobcode, [mach-id], Max(RunNumber) as RunNumber Resident LAS Group By description, kjobcode, [mach-id];
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;