Script Issue - Date Format

[amit_saini]

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.

[sunny_talwar]

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;

[sunny_talwar]

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

[amit_saini]

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.

[sunny_talwar]

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?

[amit_saini]

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

[sunny_talwar]

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

[amit_saini]

PFA

[sunny_talwar]

I have added few TRACE on Zeit tab, can you share the result of those from the log file when you run this.