struck with this code regarding incremental update

[Anonymous]

Hi,

I am struck with this code regarding incremental update.Can anyone please help me.WHERE NOT EXISTS not working.Thank you.

Prodincre:

LOAD ID,

     Product,

     Sales,

     Modified_Date

FROM

incremental_load.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE(Modified_Date > '$(Last_Updated_Date)'   ); //this is a string var

Concatenate

LOAD ID,

     Product,

     Sales,

     Modified_Date

FROM

Product.qvd

(qvd)

Where Not Exists(ID);

[Gysbert_Wassenaar]

The code looks fine. What indicates that it isn't working?

Can you post an example qvw and source files that demonstrate the problem?

[Anonymous]

Hi here is the snapshot of what is coming .I want to eliminate duplicate record in incremental load. Here

Prod:

LOAD ID,

     Product,

     Sales,

     Modified_Date

FROM

Product.qvd

(qvd);

// A table with a new field MaxDate;

[Last updated date]:

LOAD

max(Modified_Date) as MaxDate

Resident Prod;

//Pull the MaxDate field from first row into a variable

let Last_Updated_Date = peek(MaxDate,0,[Last updated date]); //this is a string type

drop Table Prod;

Prodincre:

LOAD ID,

     Product,

     Sales,

     Modified_Date

FROM

incremental_load.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE(Modified_Date > '$(Last_Updated_Date)'   ); //this is a string var

Concatenate

LOAD ID,

     Product,

     Sales,

     Modified_Date

FROM

Product.qvd

(qvd)

Where Not Exists(ID);

//for deleteting qvd record whre the record is not there in xlsx

inner Join

LOAD ID,

     Product,

     Sales,

     Modified_Date

FROM

incremental_load.xlsx

(ooxml, embedded labels, table is Sheet1);

store Prodincre into Product.qvd(qvd);

drop table Prodincre;

  modified:

LOAD ID,

     Product,

     Sales,

     Modified_Date

FROM

Product.qvd

(qvd);

[sudeepkm]

normally there should be a check for the QVD exists or not prior concatenating the data from QVD. For first time load of this script (when QVD does not exist or not yet created) will give load error.

[Anonymous]

Hi Sudeep,

QVD is there thing is duplicate record is not eliminated .

Thank you.

[sudeepkm]

what is the value of Last_Updated_Date (MaxDate) from qvd ?

[Anonymous]

Hi Sudeep,

The maxdate is max(Modified_Date) as MaxDate from qvd;

for the above code  insert and delete are working only duplicate record is not removed with NOT EXsits.

Thank You

[Anonymous]

Just for testing purposes, see if this works.

Prodincre:

LOAD ID,

ID as ID_EXCL,

     Product,

     Sales,

     Modified_Date

FROM

incremental_load.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE(Modified_Date > '$(Last_Updated_Date)'   ); //this is a string var

Concatenate

LOAD ID,

     Product,

     Sales,

     Modified_Date

FROM

Product.qvd

(qvd)

Where Not Exists(ID_EXCL ,ID);

Drop field ID_EXCL;

[Anonymous]

Hi Wallo,

Thank you for your response. I tested the you have given . It is still showing the duplicate record.

Thank you

[Anonymous]

Are you sure both duplicate records aren't just coming from the Excel sheet?