topic Re: Script Issue - Date Format in QlikView

Script Issue - Date Format

amit_saini — Wed, 31 Oct 2018 11:19:34 GMT

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

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.

Re: Script Issue - Date Format

sunny_talwar — Wed, 31 Oct 2018 11:23:33 GMT

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

EXIT SCRIPT;

Re: Script Issue - Date Format

sunny_talwar — Wed, 31 Oct 2018 11:38:27 GMT

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

Re: Script Issue - Date Format

amit_saini — Wed, 31 Oct 2018 13:22:29 GMT

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.

Re: Script Issue - Date Format

sunny_talwar — Wed, 31 Oct 2018 13:35:56 GMT

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?

Re: Script Issue - Date Format

amit_saini — Wed, 31 Oct 2018 15:31:05 GMT

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,

Re: Script Issue - Date Format

sunny_talwar — Wed, 31 Oct 2018 15:33:48 GMT

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

Re: Script Issue - Date Format

amit_saini — Wed, 31 Oct 2018 15:41:36 GMT

PFA

Re: Script Issue - Date Format

sunny_talwar — Wed, 31 Oct 2018 15:52:03 GMT

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