topic Re: Incremental load query in QlikView

Incremental load query

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

Hi friends,

I am trying below script for incremental load but it is not working, first it says ‘Date’ is not found in qvd –I have checked many times it is available and there is data in it and given exact name as well, and database source is sqlexpress and it is giving me error msg ‘>’ is not recognised, any thoughts or ideas how to overcome this.

vQVDPath='C:\Users\madhu\Desktop\Qvd\ ';

let vExecTime=GMT();

set vlastExecTime=0;

if not IsNull(QvdCreateTime('$(vQVDPath)Goods.qvd'))then

Loadtime:

LOAD max(Date)

From $(vQVDPath);

let vlastExecTime=Peek('Date',0,'Loadtime');

Drop table Loadtime;

end if

Goods:

sql select *

From [Bfc].[dbo].[Orders]

where [Date]>$(vlastExecTime);

if not IsNull(QvdCreateTime('$(vQVDPath)Goods.qvd' '))THEN

Concatenate(Goods)

load * from $(vQVDPath)Goods.qvd;

end if;

store Goods into $(vQVDPath)Goods.qvd;

Re: Incremental load query

sasiparupudi1 — Thu, 03 Aug 2017 11:47:54 GMT

LOAD max(Date) as Date

Re: Incremental load query

arvind_patil — Thu, 03 Aug 2017 11:56:36 GMT

HI Madhu,

There is issue in your date format which is differnt in sql and you passing mentiend below try to make as DD-MMM-YYYY:

where [Date]>$(vlastExecTime);

Where Date is in differnt format and your variable in different.

Thanks,

Arvind Patil

Re: Incremental load query

Kushal_Chawda — Thu, 03 Aug 2017 12:13:53 GMT

vQVDPath='C:\Users\madhu\Desktop\Qvd\ ';

let vExecTime=GMT();

set vlastExecTime=0;

if not IsNull(QvdCreateTime('$(vQVDPath)Goods.qvd'))then

Loadtime:

LOAD max(Date) as Date

From $(vQVDPath);

let vlastExecTime=date(Peek('Date',0,'Loadtime'),'DD/MM/YYYY');

Drop table Loadtime;

end if

Goods:

sql select *

From [Bfc].[dbo].[Orders]

where to_date(to_char([Date],'DD/MM/YYYY'),'DD/MM/YYYY')> to_date('$(vlastExecTime)','DD/MM/YYYY');

if not IsNull(QvdCreateTime('$(vQVDPath)Goods.qvd' '))THEN

Concatenate(Goods)

load * from $(vQVDPath)Goods.qvd;

end if;

store Goods into $(vQVDPath)Goods.qvd;

Re: Incremental load query

madhuqliklondon — Thu, 03 Aug 2017 13:02:19 GMT

In sql 'DATE' is 20170101 format .

Re: Incremental load query

madhuqliklondon — Thu, 03 Aug 2017 13:06:19 GMT

Error now: Microsoft OLE DB Provider for SQL Server, Error Msg: 'to_char' is not a recognized built-in function name option.

Re: Incremental load query

sasiparupudi1 — Thu, 03 Aug 2017 17:55:41 GMT

Try

Set vQVDPath='C:\Users\madhu\Desktop\Qvd\ ';

let vExecTime=GMT();

set vlastExecTime=0;

if not IsNull(QvdCreateTime('$(vQVDPath)Goods.qvd'))then

Loadtime:

LOAD max(Date) as Date

From $(vQVDPath)Goods.qvd;

Let vlastExecTime=Date(Peek('Date',0,'Loadtime'),'YYYYMMDD');

Drop table Loadtime;

else

Let vlastExecTime=Date(GMT(),'YYYYMMDD');

end if

Goods:

sql select *

From [Bfc].[dbo].[Orders]

where [Date]>$(vlastExecTime);

if not IsNull(QvdCreateTime('$(vQVDPath)Goods.qvd' '))THEN

Concatenate(Goods)

load * from $(vQVDPath)Goods.qvd;

end if;

store Goods into $(vQVDPath)Goods.qvd;

Re: Incremental load query

rwunderlich — Thu, 03 Aug 2017 18:16:07 GMT

If you get an error on:

LOAD max(Date) as Date

then

Let vlastExecTime=Date(Peek('Date',0,'Loadtime'),'YYYYMMDD');

will not work as there is no "Date" field to peek.

then

where [Date]>$(vlastExecTime);

will not work as the vLastExecTime variable was not initialized by the peek.

I suggest you have to resolve the max(Date) error before looking at any downstream issues. Can you post the document log?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com