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]

Sunny, tried that but see below expression. 

sum({<RunNumber={'=Max(RunNumber)'}>}[event-qty])

 Original table

its resulting in

Output

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.

[sunny_talwar]

Try this

FirstSortedValue(Aggr(Sum([event-qty]), RunNumber), -RunNumber)

[bnelson111]

Its has unfortunately dropped the Slit LAS from kjobcode 211779, well it actually has subtracted it from the Print LAS

[sunny_talwar]

I am not sure I follow...

[bnelson111]

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;

[sunny_talwar]

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)
)

[bnelson111]

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 ;

[sunny_talwar]

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];

[bnelson111]

90% works perfectly, only one line still coming is the event qty 70,967 mach-id 3PRT run number 75, this used be excluded as the kjobcode 212258 4SL has a run number of 76. Unfortunately there will be times when mach-id 4SL and 3PRT will have different runnumbers. In that case the one with the larger Runnumber must only be displayed, if both same both displayed.
Thanks

[sunny_talwar · Accepted solution]

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;