Incremental Load

[#1]

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!

[hectorgarcia]

what error message are you receiving?

for incremental load review the help file:

QVD files and Incremental Load

[its_anandrjs]

Hi,

You can read this good incremental load docs

Regards

Anand

[#4]

Hector,

My load completes without the Where clause conditions being met.

[#5]

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.

[#6]

Hector,

Here is the screenshot of the Script Error message:

[Anonymous]

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;

[Anonymous]

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

FROM 'Data_2014Q3update'

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

AND Year <> 2013;

[#9]

That returned the same error...

[maxgro]

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')