topic Re: data load error in QlikView

data load error

arethaking — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

SalesDate column is coming from database. I am trying to implement incremental load.

SalesDate values are 01SEP2016,01OCT2016, and so on.

I am using reloadtime() function.

LET lastReloadTime = num(date(ReloadTime(),'DDMMMYYYY'));

Data:

LOAD *

FROM

[..\QVD\Sales.qvd]

(qvd);

Concatenate

LOAD *;

SQL SELECT * FROM Sales_Table

where (num(date(Date#(SalesDate,'DDMMMYYYY'),'DDMMMYYYY')) > $(lastReloadTime));

Error Message:

expected something like ')' between the 'date' keyword and '('.

SQL SELECT * FROM Sales_Table

where (num(date(Date#(SalesDate,'DDMMMYYYY'),'DDMMMYYYY')) > 42647.496226852)

Please help me.

Re: data load error

sushil353 — Tue, 04 Oct 2016 04:08:51 GMT

Hi,

This might be due to (num(date(Date#(SalesDate,'DDMMMYYYY'),'DDMMMYYYY'))

why dont you simply use the date format of SalesDate field as it is and change the format of lastReloadTime accordingly..

HTH

Sushil

Re: data load error

arethaking — Tue, 04 Oct 2016 04:16:05 GMT

Hi Sushil,

My SalesDate is not in Date format in Qlikview. It is a character. That's why I am using format.

I followed your advise. Getting the below error message:

Syntax error, expected something like an 'OR' keyword or ')' between an integer and the word 'OCT2016'.

SQL SELECT * FROM Sales_Table

where (SalesDate> 04OCT2016)

Re: data load error

sushil353 — Tue, 04 Oct 2016 04:23:38 GMT

Hi,

Try this: where SalesDate> '04OCT2016'

if that works then use variable in quotes like : '$(lastReloadTime)'

HTH

Re: data load error

Anil_Babu_Samineni — Tue, 04 Oct 2016 04:54:26 GMT

I've to ask one question before help. If your reload date is today. Then what output will come using this

LOAD *;

SQL SELECT * FROM Sales_Table

where (num(date(Date#(SalesDate,'DDMMMYYYY'),'DDMMMYYYY')) > $(lastReloadTime));

Might be this, I've added this >= and then try with single quotes or without single quotes

LOAD *;

SQL SELECT * FROM Sales_Table

where (num(date(Date#(SalesDate,'DDMMMYYYY'),'DDMMMYYYY')) >= '$(lastReloadTime)');

Re: data load error

MayilVahanan — Tue, 04 Oct 2016 04:56:55 GMT

Hi Aretha

You can use To_Char function to convert the date format in database side.

Re: data load error

arethaking — Tue, 04 Oct 2016 05:00:28 GMT

Hi Mayil, Could you please provide the code. Thank you

Re: data load error

sushil353 — Tue, 04 Oct 2016 05:05:30 GMT

Some thing like this : SELECT to_char(column_date, 'yyyy/mm/dd') from table;

Re: data load error

nagarjuna_kotha — Tue, 04 Oct 2016 05:06:59 GMT

Check this app.

I hope this might helps you.

-Nagarjun

Re: data load error

nagarjuna_kotha — Tue, 04 Oct 2016 05:07:58 GMT

Re: data load error

MayilVahanan — Tue, 04 Oct 2016 05:32:38 GMT

Please check below link for sample

https://sqlandplsql.com/2012/10/28/to_char-number-function-oracle/

Re: data load error

jagan — Tue, 04 Oct 2016 05:49:03 GMT

Hi,

You can simply use

LET lastReloadTime = num(date(ReloadTime(),'DDMMMYYYY'));

TableName:

SELECT

FROM TableName

WHERE SalesDate > '$(lastReloadTime)';

Re: data load error

jagan — Tue, 04 Oct 2016 05:50:40 GMT

From your Query:

SQL SELECT * FROM Sales_Table

where (num(date(Date#(SalesDate,'DDMMMYYYY'),'DDMMMYYYY')) > $(lastReloadTime));

Note: Num#(), Num(), Date#() and Date() are all qlikview functions, it won't work in SQL Queries, you can use only in LOAD statement but not in SELECT statment.

Hope this helps you.

Regards,

Jagan.