Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Folks,
Below is my script:
let x = 0;
for i = 0 to NoOfRows('Shift')-1
let vStartTime = time#(Peek('From',i,'Shift'), 'hh:mm');
let vShiftlineKey = Peek('ShiftLineKey1',i,'Shift');
let vStartTime2 = num('$(vStartTime)');
let vShiftDuration = num(Peek('Shift duration',i,'Shift')); //8
let vShiftDurationMinutes = $(vShiftDuration) * 60;
//genearet a Strong key for Matching
Temp_Shift_Key_Time:
load
frac('$(vStartTime2)')-maketime(0,1)+maketime(floor(recno()/60),mod(recno(), 60)) as TempTimeKey,
'$(vShiftlineKey)' as ShiftLineKey
AutoGenerate($(vShiftDurationMinutes));
next
let vCurrentShiftStart ='';
let vLastShiftEnd ='';
let StartTimeCal = 0;
let vDurationMinutes = 2880;
T1:
load
*,
hour(%DateKey) as calhour,
floor(%DateKey) + maketime(hour(%DateKey)) as hoursort,
right(time(%DateKey),8) as %DateKeyJoin;
load
today()-div(recno()-1, 1440)+maketime(div(mod(recno()-1, 1440), 60), mod(recno()-1, 60)) as %DateKey
AutoGenerate($(vDurationMinutes));
left join (T1)
load
right(time(TempTimeKey),8) as %DateKeyJoin,
ShiftLineKey as ShiftLineKey1
resident Temp_Shift_Key_Time;
***************************************************************************************************
Now when I'm running the above script , I'm getting below error:
31.10.2018 11:54:24: 0465 left join (T1)
31.10.2018 11:54:24: 0466 load
31.10.2018 11:54:24: 0467 right(time(TempTimeKey),8) as %DateKeyJoin,
31.10.2018 11:54:24: 0468 ShiftLineKey as ShiftLineKey1
31.10.2018 11:54:24: 0469 resident Temp_Shift_Key_Time
31.10.2018 11:54:24: Error: Table not found
Not sure why I'm getting above error also "Temp_Shift_Key_Time:" this table is not in log files where I have Error table result.
As it is auto generated function , it could be that because of date format issue table has not been created.
Not sure how to fix that, any suggestions???
Also these are Set format defined:
IF $(vTimeFormat) = 1 then;
set vTF = 'hh:mm TT' ;
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='hh:mm:ss tt';
SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff] tt';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
ELSEIF $(vTimeFormat) = 2 then
set vTF = 'hh:mm' ;
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='$#.##0,00;($#.##0,00)';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';
ENDIF
We are using same application in 30 different location , in Germany and all other locations it is working fine , but in Belgium not.
May be add a Exist script after Next statement to check if Temp_Shift_Key_Time table is created or not
let x = 0;
for i = 0 to NoOfRows('Shift')-1
let vStartTime = time#(Peek('From',i,'Shift'), 'hh:mm');
let vShiftlineKey = Peek('ShiftLineKey1',i,'Shift');
let vStartTime2 = num('$(vStartTime)');
let vShiftDuration = num(Peek('Shift duration',i,'Shift')); //8
let vShiftDurationMinutes = $(vShiftDuration) * 60;
//genearet a Strong key for Matching
Temp_Shift_Key_Time:
load
frac('$(vStartTime2)')-maketime(0,1)+maketime(floor(recno()/60),mod(recno(), 60)) as TempTimeKey,
'$(vShiftlineKey)' as ShiftLineKey
AutoGenerate($(vShiftDurationMinutes));
next
EXIT SCRIPT;
Also, it would help to add TRACE statement to see the values that show up for these guys
let vStartTime = time#(Peek('From',i,'Shift'), 'hh:mm');
let vShiftlineKey = Peek('ShiftLineKey1',i,'Shift');
let vStartTime2 = num('$(vStartTime)');
let vShiftDuration = num(Peek('Shift duration',i,'Shift')); //8
TRACE $(vStartTime);
TRACE $(vShiftlineKey);
TRACE $(vStartTime2);
TRACE $(vShiftDuration);
Hello Sunny,
Here is the result and I do have table created after exit script:
31.10.2018 14:13:59: 45 fields found: ShiftLineKey1, Plant_Description, Region, Country, Line_LPS, Line_LPS_expand, Line2Load, Line_LPS_Desc, ShiftLine, Plattform, Shift, %Shift, ShiftLabel, From, Until, IsCurrentShift, Shift duration, Production / hour, hour (green), hour (yellow), hour (red), OEE (green), OEE (yellow), OEE (red), Scrap (green), Scrap (yellow), Scrap (red), FTQ (green), FTQ (yellow), FTQ (red), Last process step, CAQ Station, %AddInfoKey, Path1, Path2, Path3, Path4, Path5, Duration-sec, Start, Delay-sec, Line-Number, Line-Desc-for-Plant-Overview, Line-Sort-for-Plant-Overview, Line-Number-Group, 3 lines fetched
31.10.2018 14:13:59: 0406 MultiLineGroup:
31.10.2018 14:13:59: 0407 load
31.10.2018 14:13:59: 0408 trim(Line & [Last-process-step] & [CAQ-station]) as [Line-Number-Group],
31.10.2018 14:13:59: 0409 MultiLineGroup
31.10.2018 14:13:59: 0410
31.10.2018 14:13:59: 0411
31.10.2018 14:13:59: 0412 resident LineConfig where len(MultiLineGroup)>0 and ActivateExport = 1
31.10.2018 14:13:59: 2 fields found: Line-Number-Group, MultiLineGroup, 0 lines fetched
31.10.2018 14:13:59: 0420 let x = 0
31.10.2018 14:13:59: 0421 for i = 0 to NoOfRows('Shift')-1
31.10.2018 14:13:59: 0423 let vStartTime = time#(Peek('From',i,'Shift'), 'hh:mm')
31.10.2018 14:13:59: 0425 let vShiftlineKey = Peek('ShiftLineKey1',i,'Shift')
31.10.2018 14:13:59: 0426 let vStartTime2 = num('06:00')
31.10.2018 14:13:59: 0427 let vShiftDuration = num(Peek('Shift duration',i,'Shift'))
31.10.2018 14:13:59: 0429 let vShiftDurationMinutes = 8 * 60
31.10.2018 14:13:59: 0431 TRACE06:00
31.10.2018 14:13:59: Error: Unknown statement
31.10.2018 14:14:19: 0432 TRACE1|4
31.10.2018 14:14:19: Error: Unknown statement
31.10.2018 14:14:25: 0433 TRACE0,25
31.10.2018 14:14:25: Error: Unknown statement
31.10.2018 14:14:28: 0434 TRACE8
31.10.2018 14:14:28: Error: Unknown statement
31.10.2018 14:14:30: 0439 Temp_Shift_Key_Time:
31.10.2018 14:14:30: 0440 load
31.10.2018 14:14:30: 0441 frac('0,25')-maketime(0,1)+maketime(floor(recno()/60),mod(recno(), 60)) as TempTimeKey,
31.10.2018 14:14:30: 0442
31.10.2018 14:14:30: 0443 '1|4' as ShiftLineKey
31.10.2018 14:14:30: 0444 AutoGenerate(480)
31.10.2018 14:14:30: 2 fields found: TempTimeKey, ShiftLineKey, 480 lines fetched
31.10.2018 14:14:30: 0448 next
31.10.2018 14:14:30: 0423 let vStartTime = time#(Peek('From',i,'Shift'), 'hh:mm')
31.10.2018 14:14:30: 0425 let vShiftlineKey = Peek('ShiftLineKey1',i,'Shift')
31.10.2018 14:14:30: 0426 let vStartTime2 = num('14:00')
31.10.2018 14:14:30: 0427 let vShiftDuration = num(Peek('Shift duration',i,'Shift'))
31.10.2018 14:14:30: 0429 let vShiftDurationMinutes = 8 * 60
31.10.2018 14:14:30: 0431 TRACE14:00
31.10.2018 14:14:30: Error: Unknown statement
31.10.2018 14:14:32: 0432 TRACE2|4
31.10.2018 14:14:32: Error: Unknown statement
31.10.2018 14:14:34: 0433 TRACE0,58333333333333
31.10.2018 14:14:34: Error: Unknown statement
31.10.2018 14:14:40: 0434 TRACE8
31.10.2018 14:14:40: Error: Unknown statement
31.10.2018 14:14:42: 0439 Temp_Shift_Key_Time:
31.10.2018 14:14:42: 0440 load
31.10.2018 14:14:42: 0441 frac('0,58333333333333')-maketime(0,1)+maketime(floor(recno()/60),mod(recno(), 60)) as TempTimeKey,
31.10.2018 14:14:42: 0442
31.10.2018 14:14:42: 0443 '2|4' as ShiftLineKey
31.10.2018 14:14:42: 0444 AutoGenerate(480)
31.10.2018 14:14:42: 2 fields found: TempTimeKey, ShiftLineKey, 960 lines fetched
31.10.2018 14:14:42: 0448 next
31.10.2018 14:14:42: 0423 let vStartTime = time#(Peek('From',i,'Shift'), 'hh:mm')
31.10.2018 14:14:42: 0425 let vShiftlineKey = Peek('ShiftLineKey1',i,'Shift')
31.10.2018 14:14:42: 0426 let vStartTime2 = num('22:00')
31.10.2018 14:14:42: 0427 let vShiftDuration = num(Peek('Shift duration',i,'Shift'))
31.10.2018 14:14:42: 0429 let vShiftDurationMinutes = 8 * 60
31.10.2018 14:14:42: 0431 TRACE22:00
31.10.2018 14:14:42: Error: Unknown statement
31.10.2018 14:14:45: 0432 TRACE3|4
31.10.2018 14:14:45: Error: Unknown statement
31.10.2018 14:14:48: 0433 TRACE0,91666666666667
31.10.2018 14:14:48: Error: Unknown statement
31.10.2018 14:14:51: 0434 TRACE8
31.10.2018 14:14:51: Error: Unknown statement
31.10.2018 14:14:55: 0439 Temp_Shift_Key_Time:
31.10.2018 14:14:55: 0440 load
31.10.2018 14:14:55: 0441 frac('0,91666666666667')-maketime(0,1)+maketime(floor(recno()/60),mod(recno(), 60)) as TempTimeKey,
31.10.2018 14:14:55: 0442
31.10.2018 14:14:55: 0443 '3|4' as ShiftLineKey
31.10.2018 14:14:55: 0444 AutoGenerate(480)
31.10.2018 14:14:55: 2 fields found: TempTimeKey, ShiftLineKey, 1.440 lines fetched
31.10.2018 14:14:55: 0448 next
31.10.2018 14:14:55: 0450 exit Script
31.10.2018 14:14:55: $Syn 1 = Line-Number+Region+Country+Line-Desc-for-Plant-Overview+Line-Sort-for-Plant-Overview+Shift+Path1+Path2+Path3+Path4+Path5+Duration-sec+Start+Delay-sec
31.10.2018 14:15:01: Execution finished.
Are you sure you are not dropping the Temp_Shift_Key_Time table after the loop ends and before you use it from left joining? Also, do you have more script after the left join?
Hi Sunny,
Sorry , yes we are droping it see below:
left join (T1)
load
right(time(TempTimeKey),8) as %DateKeyJoin,
ShiftLineKey as ShiftLineKey1
resident Temp_Shift_Key_Time;
T2:
load
*,
hour(%DateKey) as calhour,
floor(%DateKey) + maketime(hour(%DateKey)) as hoursort,
right(time(%DateKey),8) as %DateKeyJoin;
load
today()-div(recno()-1, 1440)+maketime(div(mod(recno()-1, 1440), 60), mod(recno()-1, 60)) as %DateKey
AutoGenerate($(vDurationMinutes));
left join (T2)
load
right(time(TempTimeKey),8) as %DateKeyJoin,
ShiftLineKey as ShiftLineKey11
resident Temp_Shift_Key_Time;
Time:
NoConcatenate
load
*
,num(WeekDay(%DateKey)) & '|' & hour(%DateKey) & '|' & ShiftLineKey1 as %BreakMaintenanceKey
Resident T1;
Concatenate (Time)
load
calhour,
hoursort,
%DateKey,
ShiftLineKey11 as ShiftLineKey1,
num(WeekDay(%DateKey)) & '|' & hour(%DateKey) & '|' & ShiftLineKey11 as %BreakMaintenanceKey
Resident T2;
DROP Table T1, T2;
drop Table Temp_Shift_Key_Time;
Thanks,
AS
Would it be possible to share the complete script because based on what you have shared I am not seeing anything wrong, but I am guessing there has to be something which is causing this...
PFA
I have added few TRACE on Zeit tab, can you share the result of those from the log file when you run this.