Weird Error

[Anonymous]

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

[m_woolf]

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

[swuehl]

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.

[consenit]

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.

[Anonymous]

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

[swuehl · Accepted solution]

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.

[Anonymous]

Thanks Swuehl, date format is different.