topic Re: Weird Error in QlikView

Weird Error

Anonymous — Thu, 23 Jul 2015 17:45:52 GMT

I have an application in Prod server which upon reload its getting successful, when the same has been moved to Test server its failing with an error message like field not found.

Please find the below script and their log in different servers:

TempCal:

NoConcatenate

Load Distinct

RECEIPT_DATE

//date(RECEIPT_DATE) as RECEIPT_DATE

Resident SALES_TB;

Temp:

NoConcatenate

LOAD

min(RECEIPT_DATE) AS minDate,

max(RECEIPT_DATE) AS maxDate

RESIDENT TempCal;

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));

LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

LET vToday = num(today());

DROP TABLEs Temp, TempCal;

Failure Log of Test Server

7/23/2015 9:56:02 AM: 0461 Temp:

7/23/2015 9:56:02 AM: 0462 NoConcatenate

7/23/2015 9:56:02 AM: 0463 LOAD

7/23/2015 9:56:02 AM: 0464 min(RECEIPT_DATE) AS minDate,

7/23/2015 9:56:02 AM: 0465 max(RECEIPT_DATE) AS maxDate

7/23/2015 9:56:02 AM: 0466 RESIDENT TempCal

7/23/2015 9:56:02 AM: 2 fields found: minDate, maxDate, 1 lines fetched

7/23/2015 9:56:03 AM: 0468 LET varMinDate = Num(Peek('minDate', 0, 'Temp'))

7/23/2015 9:56:03 AM: 0469 LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'))

7/23/2015 9:56:03 AM: 0471 LET vToday = num(today())

7/23/2015 9:56:03 AM: 0473 DROP TABLEs Temp, TempCal

7/23/2015 9:56:03 AM: 0475 TempCalendar_1:

7/23/2015 9:56:03 AM: 0476 LOAD

7/23/2015 9:56:03 AM: 0477 + Iterno() - 1 AS Num,

7/23/2015 9:56:03 AM: 0478 date( + Iterno() - 1) AS TempDate

7/23/2015 9:56:03 AM: 0479 AUTOGENERATE 1 WHILE +Iterno () -1 <=

7/23/2015 9:56:03 AM: Error: Field not found - <<=>

7/23/2015 9:56:23 AM: General Script Error

7/23/2015 9:56:23 AM: Execution Failed

7/23/2015 9:56:23 AM: Execution finished.

Success log of Prod server

7/23/2015 9:33:57 AM: 0461 Temp:

7/23/2015 9:33:57 AM: 0462 NoConcatenate

7/23/2015 9:33:57 AM: 0463 LOAD

7/23/2015 9:33:57 AM: 0464 min(RECEIPT_DATE) AS minDate,

7/23/2015 9:33:57 AM: 0465 max(RECEIPT_DATE) AS maxDate

7/23/2015 9:33:57 AM: 0466 RESIDENT TempCal

7/23/2015 9:33:57 AM: 2 fields found: minDate, maxDate, 1 lines fetched

7/23/2015 9:33:57 AM: 0468 LET varMinDate = Num(Peek('minDate', 0, 'Temp'))

7/23/2015 9:33:57 AM: 0469 LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'))

7/23/2015 9:33:57 AM: 0471 LET vToday = num(today())

7/23/2015 9:33:57 AM: 0473 DROP TABLEs Temp, TempCal

7/23/2015 9:33:57 AM: 0475 TempCalendar_1:

7/23/2015 9:33:57 AM: 0476 LOAD

7/23/2015 9:33:57 AM: 0477 41276 + Iterno() - 1 AS Num,

7/23/2015 9:33:57 AM: 0478 date(41276 + Iterno() - 1) AS TempDate

7/23/2015 9:33:57 AM: 0479 AUTOGENERATE 1 WHILE 41276+Iterno () -1 <= 42208

7/23/2015 9:33:57 AM: 2 fields found: Num, TempDate, 933 lines fetched

john

Re: Weird Error

m_woolf — Thu, 23 Jul 2015 18:08:40 GMT

I don't believe you can use MIN in script without groupby

Re: Weird Error

swuehl — Thu, 23 Jul 2015 18:10:35 GMT

I assume these two Servers use different default date formats (probably set in OS). One can't be Interpreted by QV hence numeric functions in variable assignment fail.

Re: Weird Error

consenit — Thu, 23 Jul 2015 18:13:28 GMT

Hi there.

This is the failing sentence:

7/23/2015 9:33:57 AM: 0471 LET vToday = num(today())

For some reason QlikView fails to assign any value to the variable vToday resulting in two syntax errors later in the script.

I have no idea why though, try executing script on the test server step by step.

Kind regards,

Ernesto.

Re: Weird Error

Anonymous — Thu, 23 Jul 2015 18:22:16 GMT

I used the same script for Ship date, Pick date and Receipt Date in the application in different tabs, its working fine for the Ship date, pick date but not for Receipt date.

John

Re: Weird Error

swuehl — Thu, 23 Jul 2015 23:14:30 GMT

This could happen when the data source(s) delivers differently formatted three date fields.

I would suggest using an exit script after loading TempCal and looking closely at the field formats.

RECEIPT_DATE must have a numeric representation to allow you to apply min() and max() successfully.

Have you checked the table viewer, what is the content of Temp table line? I assume it shows only NULL on Test server.

Re: Weird Error

Anonymous — Fri, 24 Jul 2015 12:32:18 GMT

Thanks Swuehl, date format is different.