topic Re: Incremental Loading problem (Insert, Update Delete) in QlikView

Incremental Loading problem (Insert, Update Delete)

Wed, 07 Sep 2016 07:52:37 GMT

Hello

This is my first post on the community although I have learn almost everything I know for Qlikview from it the last 3 years.

So thank you qlikview and qlikview fans for this massive knowledge offered so widely.

I have a problem with incremental loading script getting an error message as seen below.

Also I am attaching the script file I am using.

(the DB is SQLSERVER 2008)

The COSTMAIN_COSTDATE is (datetime , null) field and the select gives 2016-05-04 00:00:00.000.

Thanks in advance for any help provided

Michael M.

Re: Incremental Loading problem (Insert, Update Delete)

swuehl — Wed, 07 Sep 2016 19:08:37 GMT

If you want to access a table on a database server, you need to establish a database connection and then use a SELECT statement to load the data.

A LOAD statement can be used as preceding load statement on top of the SQL select, but not as a standalone statement to access the DB.

Re: Incremental Loading problem (Insert, Update Delete)

Thu, 08 Sep 2016 13:50:12 GMT

Mr Wuhl

Thanks for your reply.

The database connection is on the MAIN TAB and the script runs smoothly up to the point that the variable '$(LAST_UPDATED_DATE)' gives the value on the where clause. So except from the connection I must have an incorrect load order or syntax on my script.because

So I made some changes replacing LOAD with select and I am getting a new error message (attached).

Probably I messed the script up.

Please advice

thanks in advance

Michael

Re: Incremental Loading problem (Insert, Update Delete)

Ricardo_Gerhard — Thu, 08 Sep 2016 14:07:29 GMT

Dear Michael,

Try to use the Wizard to connect.

Anyway, bellow, two videos to help you how connect to databases.

Loading ODBC Data into QlikView - YouTube

QlikView - Connect and Query Data - YouTube

Please, mark the CORRECT/HELPFULL comments.

Regards,

Ricardo Gerhard

Re: Incremental Loading problem (Insert, Update Delete)

Clever_Anjos — Thu, 08 Sep 2016 14:10:55 GMT

Does z_constmain is a SQL table? If yes, please use SQL Select instead of LOAD

Re: Incremental Loading problem (Insert, Update Delete)

swuehl — Thu, 08 Sep 2016 16:03:43 GMT

Try enclosing your variable in the WHERE clause in single quotes (looks like you did this in the attached script, but can't see the single quotes in your first screenshot) and also format the variable as timestamp:

//Store Last Modified Date

let LAST_UPDATED_DATE = Timestamp(PEEK('MaxDate',0,'LAST_UPDATED_DATE'),'YYYY-MM-DD hh:mm:ss.fff');

[...]

WHERE

COSTMAIN_COSTDATE > '$(LAST_UPDATED_DATE)';

Re: Incremental Loading problem (Insert, Update Delete)

Fri, 09 Sep 2016 11:19:32 GMT

Mr Wuhl thanks for your reply

Please spare 5 minutes to see some screen shots.

Thanks for the HELP to you an all community

Re: Incremental Loading problem (Insert, Update Delete)

swuehl — Sat, 10 Sep 2016 15:15:06 GMT

Try to add an

Exit Script;

statement just before the line with the STORE statement (it seems this line is throwing an error, right?) and check your data model created up to this using the table viewer (CTRL-T).

Which tables do you see and how many rows do each table show?

Re: Incremental Loading problem (Insert, Update Delete)

Mon, 12 Sep 2016 10:52:07 GMT

Yes,

This was it. The 'Exit Script;'

Thank you very much for the precious knowledge transfer.

Very much obliged Mr Wuhl.

Re: Incremental Loading problem (Insert, Update Delete)

swuehl — Mon, 12 Sep 2016 15:41:50 GMT

Just to clarify, the 'exit script;' should just help you finding the cause of your issue, if you leave it in your script, your QVD is probably not getting updated.

So, I assume the issue was something else, right?

Re: Incremental Loading problem (Insert, Update Delete)

Tue, 13 Sep 2016 08:26:25 GMT

Mr Wuhl

I don't know why but seems to work and update the qvd after the concatenation even though the 'Exit Script' command is placed before the Replace.

Thanks for your time you spend helping me.

I ll let you know when I will give the qvw to users.

COSTMAIN_EndINVENTORYCOST_AcnTotalCost,

COSTMAIN_ENDCOGS_DayHarvMedCost*/

FROM COSTMAINQVD.QVD (QVD) WHERE NOT EXISTS(CostmainID);

INNER JOIN

select CostmainID FROM z_costmain;

Exit Script;

//REPLACE OLD QVD FILE

Store Incemental into ; //COSTMAINQVD.QVD(qvd);

//Drop Incremental table

Drop Table Incemental;