topic Re: struck with this code regarding incremental update in QlikView

struck with this code regarding incremental update

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: struck with this code regarding incremental update

Gysbert_Wassenaar — Wed, 01 Nov 2017 10:39:07 GMT

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

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

Re: struck with this code regarding incremental update

Anonymous — Wed, 01 Nov 2017 12:13:30 GMT

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

Re: struck with this code regarding incremental update

sudeepkm — Wed, 01 Nov 2017 12:48:09 GMT

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.

Re: struck with this code regarding incremental update

Anonymous — Wed, 01 Nov 2017 12:53:57 GMT

Hi Sudeep,

QVD is there thing is duplicate record is not eliminated .

Thank you.

Re: struck with this code regarding incremental update

sudeepkm — Wed, 01 Nov 2017 13:40:20 GMT

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

Re: struck with this code regarding incremental update

Anonymous — Wed, 01 Nov 2017 14:18:56 GMT

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

Re: struck with this code regarding incremental update

Anonymous — Wed, 01 Nov 2017 14:43:28 GMT

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;

Re: struck with this code regarding incremental update

Anonymous — Wed, 01 Nov 2017 15:00:55 GMT

Hi Wallo,

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

Thank you

Re: struck with this code regarding incremental update

Anonymous — Wed, 01 Nov 2017 15:03:17 GMT

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

Re: struck with this code regarding incremental update

sudeepkm — Wed, 01 Nov 2017 15:06:44 GMT

I would like you to test the below script in a new qvw. Rename your existing qvd to somethingelse.

Also did you have duplicate records in your source xls file when first time you ran the script?

let vLast_Updated_Date = '12/31/1900';

if QvdCreateTime('Product.qvd')>0 then

TMP:

LOAD max(Modified_Date) as mxdt FROM Product.qvd(qvd);

let vLast_Updated_Date = peek('mxdt',0,'TMP');

DROP Table TMP;

end if

Product:

LOAD ID, Product, Sales, Modified_Date

FROM incremental_load.xlsx (ooxml, embedded labels, table is Sheet1) Where Modified_Date > '$(vLast_Updated_Date)';

if QvdCreateTime('Product.qvd')>0 then

Concatenate

LOAD ID, Product, Sales, Modified_Date

FROM Product.qvd(qvd) Where not Exists (ID);

end if

inner Join

LOAD ID, Product, Sales, Modified_Date

FROM incremental_load.xlsx (ooxml, embedded labels, table is Sheet1);

STORE Product into Product.qvd(qvd);

Re: struck with this code regarding incremental update

madhumitha — Wed, 01 Nov 2017 15:09:43 GMT

HI Lavanya,

I am not very sure, but I think NOT EXIST condition works on Resident tables alone.

Can you try taking a resident load?

Re: struck with this code regarding incremental update

Anonymous — Fri, 03 Nov 2017 22:56:29 GMT

Hi Sudeep,

Your code also gave the same result. Not Exists is not worked here.

thank you

Re: struck with this code regarding incremental update

Anonymous — Fri, 03 Nov 2017 23:02:31 GMT

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

Can you share your excel file and qvw?

Re: struck with this code regarding incremental update

Anonymous — Fri, 03 Nov 2017 23:39:52 GMT

Hi Willow,

This is the result of the above code.'Not Exists' not working here.

Thank you

Re: struck with this code regarding incremental update

Anonymous — Fri, 03 Nov 2017 23:45:57 GMT

I don't want the result of the script, I want to see what is in your incremental_load.xlsx spreadsheet on Sheet1.