topic Re: Incremental Load with max filetime getting error in QlikView

Incremental Load with max filetime getting error

mrichman — Sat, 16 Nov 2024 04:52:04 GMT

Hi All,

I'm trying to build an incremental reload with max filetime however, I'm receiving the underlying error the whole time.

Do you guys probably know on how to deal with this?

LET AND MAXDATE CALCULATION:

MaxDate:
LOAD
max(Date([FileTime()],'DD-MM-YYYY HH:MM:SS')) as Max_Date
Resident Final;

LET Last_updated_date = peek('Max_Date',0,'Last_updated_date');

ERROR:

Syntax error Unexpected token: '08', expected nothing
Where FileTime() > 12-12-2018 >>>>>>08<<<<<<:12:36

Thanks in advance!

Re: Incremental Load with max filetime getting error

prieper — Wed, 02 Jan 2019 10:56:42 GMT

Think that you should use
FileTime() is a function referring to the currently loaded file (not for RESIDENT).

Script might be (aircode):
Data: LOAD *, FILETIME() AS FileTime FROM ....;
Max: LOAD MAX(FileTime) AS MaxDate RESIDENT Data;
...

HTH Peter

Re: Incremental Load with max filetime getting error

mrichman — Wed, 02 Jan 2019 11:01:03 GMT

Hi Peter,
Thanks for your prompt reply. Yes, you are right. I have tested your recommendation but I'm still receiving the same error.

Re: Incremental Load with max filetime getting error

prieper — Wed, 02 Jan 2019 11:06:00 GMT

The PEEK-command should point to the correct table, in your example "MaxDate".
Then the PEEK should read: PEEK('MaxDate', 0, 'MaxDate')

Re: Incremental Load with max filetime getting error

sivakumar1994 — Wed, 02 Jan 2019 11:16:50 GMT

The FileTime() function requires the filename for resident load. PFB syntax.

Syntax:

FileTime([ filename ])

Or FileTime() will be generated without filename parameter when data is read from file.

Example Result

LOAD *, FileTime( ) as X from abc.txt;	Will return the date and time of the last modification of the file (abc.txt) as a timestamp in field X in each record read.
FileTime( 'xyz.xls' )	Will return the timestamp of the last modification of the file xyz.xls.

Re: Incremental Load with max filetime getting error

Gysbert_Wassenaar — Wed, 02 Jan 2019 11:34:08 GMT

ERROR:
Syntax error Unexpected token: '08', expected nothing
Where FileTime() > 12-12-2018 >>>>>>08<<<<<<:12:36

Looks like you're using $(Last_updated_date) in your where clause: Where FileTime() > $(Last_updated_date). Since the variable value contains several spaces Qlik thinks you're trying specify multiple conditions in the where clause. But 08 is not an operator like AND or OR it throws an error. You can try Where FileTime() > '$(Last_updated_date)'. Or if that doesn't work use only the numeric value: Num(max([FileTime]))) as Max_Date

Re: Incremental Load with max filetime getting error

mrichman — Wed, 02 Jan 2019 13:07:29 GMT

Hi Gysbert,

Getting a similar error:

Syntax error
Unexpected token: ',', expected nothing
Where Date(FileTime(),'MM-DD-YYYY') = 43446>>>>>>,<<<<<<374027778

Any ideas? Thanks in advance.

Re: Incremental Load with max filetime getting error

Gysbert_Wassenaar — Wed, 02 Jan 2019 13:31:08 GMT

Ok, so your decimal symbol setting is a comma instead of a dot. Try replacing that comma with a dot.

LET Last_updated_date = Replace(peek('Max_Date',0,'Last_updated_date'), ',' , '.' );

Re: Incremental Load with max filetime getting error

mrichman — Thu, 03 Jan 2019 08:21:20 GMT

Worked, Thanks!