topic Re: Incremental Load Error in QlikView

Incremental Load Error

Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Incremental Load Error

sunny_talwar — Tue, 08 Mar 2016 21:14:42 GMT

Why are you doing SQL SELECT * for QVD load?

May be you want this instead

Concatenate

LOAD *

FROM customercuration.qvd (qvd)

Where not Exists (pkdim1customercuration);

Re: Incremental Load Error

maxgro — Tue, 08 Mar 2016 21:15:17 GMT

maybe (bold = change)

ODBC connect.....

CustomerCurationIU

SQL Select ........;

concatenate (CustomerCurationIU)

load *

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

where not exists (pk....);

store.......

drop......

Re: Incremental Load Error

Tue, 08 Mar 2016 21:19:11 GMT

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

Re: Incremental Load Error

sunny_talwar — Tue, 08 Mar 2016 21:22:29 GMT

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.

Re: Incremental Load Error

maxgro — Tue, 08 Mar 2016 21:27:46 GMT

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......

Re: Incremental Load Error

Tue, 08 Mar 2016 21:28:38 GMT

It only shows 138 rows

Re: Incremental Load Error

Tue, 08 Mar 2016 21:33:07 GMT

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

Re: Incremental Load Error

sunny_talwar — Tue, 08 Mar 2016 21:33:48 GMT

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

Re: Incremental Load Error

Tue, 08 Mar 2016 21:42:17 GMT

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

Re: Incremental Load Error

Wed, 09 Mar 2016 13:20:09 GMT

Can someone please provide assistance regarding the above. Could it be because I'm resaving (replacing) the incremented QVDs file in the save folder as my original QVDs were stored?

Thanks,

Re: Incremental Load Error

sunny_talwar — Wed, 09 Mar 2016 13:21:41 GMT

Would you be able to post script as a text file instead of a screenshot. It makes it difficult to use the code you have.

Re: Incremental Load Error

pooja_sn — Wed, 09 Mar 2016 13:28:57 GMT

Updating the same incremented QVDs file will definitely not cause this issue. Please share your script if you can.

Why have you included " WHERE not Exists(pkdim1customercuration);" in script?

Re: Incremental Load Error

Wed, 09 Mar 2016 13:31:46 GMT

Please find attached the text file.

Thanks,

Re: Incremental Load Error

pooja_sn — Wed, 09 Mar 2016 13:37:46 GMT

Check your database table to confirm that you have records having ' updatedate ' actually greater than $(vLastRunTime).

Re: Incremental Load Error

sunny_talwar — Wed, 09 Mar 2016 13:40:06 GMT

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;

Re: Incremental Load Error

Wed, 09 Mar 2016 14:10:58 GMT

Because that's the primary key needed for insert and update incremental load.

Re: Incremental Load Error

Wed, 09 Mar 2016 14:29:07 GMT

Still no fruition Sunny.

The first script gave me the 200K and the second script gave 300 rows.

SET vQVDLocation='..\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;

----------------------------------------------------

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;

Re: Incremental Load Error

sunny_talwar — Wed, 09 Mar 2016 14:33:34 GMT

Are you sure that pkdim1customercuration is unique identifier from your table?

Re: Incremental Load Error

Wed, 09 Mar 2016 14:39:58 GMT

Yes pkdim1customercuration is the unqiue identifier since it has no null value in the table indicating it has a value for every row in the table.