Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
madhuqliklondon
Creator II
Creator II

Incremental load query

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;

7 Replies
sasiparupudi1
Master III
Master III

LOAD max(Date) as Date

arvind_patil
Partner
Partner

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

Kush
MVP
MVP

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;

madhuqliklondon
Creator II
Creator II
Author

In sql 'DATE' is 20170101 format .

madhuqliklondon
Creator II
Creator II
Author

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

sasiparupudi1
Master III
Master III

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;

rwunderlich
Luminary Alumni
Luminary Alumni

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