Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Incremental Load Error

Hi All,

I'm trying to do a Incremental Insert and Update Load on Update Date. I'm getting a syntax error. Can someone provide assistance.

Thanks in Advance,

Capture.PNG

Capture1.PNG

1 Solution

Accepted Solutions
sunny_talwar

Try this in 2 reloads:

1) reload with this script

SET vQVDLocation=' C:\QV Project\Laura\Data';
LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');
ODBC CONNECT TO ExcessionProd;

CustomerCuration:
SQL SELECT *
FROM "rbc_dw"."open".dim1customercuration;

STORE CustomerCuration INTO $(vQVDLocation)\CustomerCuration.qvd (qvd);

DROP Table CustomerCuration;


2) once you have the qvd created, comment out the above script and replace it with this one


//SET vQVDLocation=' C:\QV Project\Laura\Data';
//LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');
//ODBC CONNECT TO ExcessionProd;
//
//CustomerCuration:
//SQL SELECT *
//FROM "rbc_dw"."open".dim1customercuration;
//
//STORE CustomerCuration INTO $(vQVDLocation)\CustomerCuration.qvd (qvd);
//
//DROP Table CustomerCuration;



SET vQVDLocation=' C:\QV Project\Laura\Data';

ODBC CONNECT TO ExcessionProd;

CustomerCuration:
SQL SELECT *
FROM "rbc_dw"."open".dim1customercuration
Where updatedate > '$(vLastRunTime)';

Concatenate(CustomerCuration)
LOAD *
FROM customercuration.qvd (qvd)
Where not exists (pkdim1customercuration);

STORE CustomerCuration INTO $(vQVDLocation)\CustomerCuration.qvd (qvd);


LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');

DROP Table
CustomerCuration;

View solution in original post

23 Replies
sunny_talwar

Why are you doing SQL SELECT * for QVD load?

Capture.PNG

May be you want this instead

Concatenate

LOAD *

FROM customercuration.qvd (qvd)

Where not Exists (pkdim1customercuration);

maxgro
MVP
MVP

maybe (bold = change)

ODBC connect.....

CustomerCurationIU

SQL Select ........;

concatenate (CustomerCurationIU)

load *

from .......qvd (qvd)

where not exists (pk....);

store.......

drop......

Not applicable
Author

Guys I'm new to this so please forebear with me. Your correction works great, With this Insert and Update Load will I lose my old data? I loaded and it showed only 138 rows while I know the DB table has around 200K rows.

Please provide insights.

Thanks

sunny_talwar

You should not. The only thing you should loose from the qvd are the new pkdim1customercuration which you are updating with new data from the SQL. So if your qvd had 10,000 rows of data and the  SQL updated 10 of them and 10 were new. You should have 10010 rows of data in the qvd after the reload.

maxgro
MVP
MVP

you have a where updatedate > ......    in the

SQL select ......

maybe it filter the rows from your database

to check how many rows you get from the db, comment the last part of the script and try

ODBC connect.....

CustomerCurationIU

SQL Select ........;

//concatenate (CustomerCurationIU)

//load *

//from .......qvd (qvd)

//where not exists (pk....);

//store.......

//drop......

Not applicable
Author

It only shows 138 rows

Capture1.PNG

Capture.PNG

Not applicable
Author

I still get 138 rows. Still don't know the reason.

Capture.PNG

Capture1.PNG

sunny_talwar

As maxgro‌ 1st create a new qvd using

One Time:

ODBC connect.....

CustomerCurationIU

SQL Select ........;


STORE CustomerCurationIU into customercuration.qvd (qvd);

LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');

This will tell you how many rows of data you have.

Now setup your incremental load


Incremental:

ODBC connect.....

CustomerCurationIU

SQL Select ........

Where updatedate > '$(vLastRunTime)';


Concatenate

LOAD *

FROM CustomerCurationIU

WHERE not Exists(pkdim1customercuration);


STORE CustomerCurationIU into customercuration.qvd (qvd);

LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');

Not applicable
Author

I still don't get the big chunk except for the 138 rows.

Capture.PNGCapture1.PNG