Incremental load query

[madhuqliklondon]

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;

[sasiparupudi1]

LOAD max(Date) as Date

[arvind_patil]

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

[Kushal_Chawda]

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]

In sql 'DATE' is 20170101 format .

[madhuqliklondon]

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

[sasiparupudi1]

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]

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