topic Incremental Load in QlikView

Incremental Load

Mon, 15 Dec 2014 21:34:45 GMT

Hi,

I am trying to write an incremental load statement however my where clause is failing me. Could you kindly guide me on what I might be doing wrong:

Data_2014Q3update:

LOAD * FROM

(qvd);

SQL Select *

FROM 'Data_2014Q3update'

WHERE [Vendor Name] <> 'ABC, INC'

AND Year <> 2013;

Thank you!

Re: Incremental Load

hectorgarcia — Mon, 15 Dec 2014 21:39:19 GMT

what error message are you receiving?

for incremental load review the help file:

QVD files and Incremental Load

Re: Incremental Load

its_anandrjs — Mon, 15 Dec 2014 21:42:39 GMT

Hi,

You can read this good incremental load docs

Regards

Anand

Re: Incremental Load

Mon, 15 Dec 2014 21:47:46 GMT

Hector,

My load completes without the Where clause conditions being met.

Re: Incremental Load

Mon, 15 Dec 2014 21:50:45 GMT

Anand,

I have read these documents multiple times, however they did not help.

I might have issues with my syntax:

1) Within the Where clause my field name has a "space". Am I using the right syntax in this case?

2) I am trying to load all data except the conditions identified in where clause. I am looking to confirm the syntax in this case.

Re: Incremental Load

Mon, 15 Dec 2014 22:15:14 GMT

Hector,

Here is the screenshot of the Script Error message:

Re: Incremental Load

Anonymous — Mon, 15 Dec 2014 22:19:25 GMT

Here an example, based on a table which has a date as key field. In short terms, first it checks if the file exists, then reads all the records starting from the last date saved and as last step, stores everything into the history table, that contains the whole data.

HOPE it helps you

//CHECK FILE EXISTS (0= EXISTS , -1= NOT EXISTS)

LET var_qvdexists=isnull(QvdCreateTime('$(VAR_YOURPATH)$(VAR_YOURTABLE)'));

//IF THE FILE EXISTS, GET THE LAST DATE STORED

//ELSE START FROM TODAY - NUMBER OF MONTH (as parameter)

IF ($(var_qvdexists) = 0) THEN

YOURTABLE:

LOAD max(date(floor(DATEFIELD))) as MAXDATE

FROM $(VAR_YOURPATH)$(VAR_YOURTABLE) (qvd)

;

let var_startdate = peek('MAXDATE');

let var_startdate = date(num(var_startdate), 'YYYY.MM.DD');

Drop table YOURTABLE;

ELSE

let var_startdate = date(AddMonths(today(), -$(var_backmonths), 0), 'YYYY/MM/DD');

END IF

//INCREMENTAL LOADING

BUFFER:

LOAD

YOUR FIELDS,

DATEFIELD

;

SQL SELECT *

FROM YOURTABLE

WHERE convert(nvarchar,DATEFIELD,102) >= '$(var_startdate)'

// AND convert(nvarchar, DATEFIELD, 102) < '$(var_udate)'

;

//HISTORY FILE

History:

load *, '1' as flag resident BUFFER;

IF ($(var_qvdexists) = 0) THEN

Concatenate

load *, '1' as flag

from $(VAR_YOURPATH)$(VAR_YOURTABLE) (qvd)

WHERE NOT EXISTS (DATEFIELD, DATEFIELD)

;

ENDIF

drop field flag;

drop table BUFFER;

STORE History INTO $(VAR_YOURPATH)$(VAR_YOURTABLE);

DROP TABLE History;

Re: Incremental Load

Anonymous — Mon, 15 Dec 2014 22:21:53 GMT

reading your first post, if I understood, the sql should be:

FROM 'Data_2014Q3update'

WHERE [Vendor Name] NOT IN 'ABC, INC'

AND Year <> 2013;

Re: Incremental Load

Mon, 15 Dec 2014 22:25:47 GMT

That returned the same error...

Re: Incremental Load

maxgro — Mon, 15 Dec 2014 22:38:42 GMT

start with the sql (and also let we know which database are you using as the sql syntax depends on db)

SQL Select *

FROM 'Data_2014Q3update';

does it works?

yes, add another line

.......

WHERE [Vendor Name] <> 'ABC, INC';

does it works?

I don't think, maybe

WHERE [Vendor Name] not in ('ABC', 'INC')

Re: Incremental Load

Mon, 15 Dec 2014 22:42:37 GMT

Massimo,

I am using QVD (QlikView Db).

Both options did not work for me!

Re: Incremental Load

maxgro — Mon, 15 Dec 2014 22:58:07 GMT

the script in bold isn't an extraction from a qvd

// this is from qvd

Data_2014Q3update:

LOAD * FROM

(qvd);

// this from a db

SQL Select *

FROM 'Data_2014Q3update'

WHERE [Vendor Name] <> 'ABC, INC'

AND Year <> 2013;