Incremental Load of 2 Files

[cbaqir]

I am trying to do an incremental load on an existing QVD. I need to do for 2 different spreadsheets monthly. The first one works but the second one doesn't. I'm getting an error related to the temp MAX_DATE table not existing and I don't know what to do.

[prieper]

give it a try with full qualified statement, think that the rowindicator is -1 by default.

Let Maxdate = floor(peek('Maxdate', 0, 'MAX_DATE'));

[cbaqir]

It's still stating MAX_DATE table not found.

[prieper]

But in the sample application, there is a table MAX_DATE with the field Maxdate (42886).

So what is the error-message saying?

There is at least one entry in the TRX_DATE, which is not recognized as number.

You may check in line 79 to place

WHERE NUM(TRX_DATE) > $(Maxdate);

This should elimate possible formatting-issues.

[cbaqir]

[prieper]

yep ...

think that the error incurs not with the first query, but somewhere in line 126 of your script.

In line 62 you drop PP_USAGE.

then you have loaded several tables with the same content, which is automatically concatenated into the first table: NEW_USAGE_CAREB. Even the PP_Usage from line 104 will be added to the above one.

Thus the query referring to PP_USAGE fails.

Either you rename the tables or change your query to NEW_USAGE_CAREB.

edit: Typo

[cbaqir]

Makes sense. Not sure why the how-to says to drop it.

If I comment out the drop for both iterations, I now get:

What am I missing?

[prieper · Accepted solution]

Dear,

QV has an automatic to concatenate all tables, which have the same fieldnames (and number of fields).

When you do no longer drop the "PP_USAGE" then the NEW_USAG_CAREB will be concatenate to the PP_USAGE and will inherit also the name.

Thus the command then should read STORE PP_USAGE INTO .....

I would always recommend to be more specific in the scripting: If you wish to CONCATENATE some data, please write it in the script (and also add the table, which to be concatenated). Similar with JOINs and KEEPs.

It may also help to insert a EXIT SCRIPT command before the failing part in the script and to check carefully, whether tables or fields do actually exists to which the error-message refers to.

Peter

[cbaqir]

Maybe my issue here is that I am taking a QVD, adding data from one spreadsheet and then adding data from another spreadsheet. The spreadsheets have different names but maybe initially pulling from the QVD and then storing into the QVD is not working.

Re: Concatenate in the script, aren't I already doing that?

[prieper]

yes: you have concatenated.

When reading the code it make sense to explicitly concatenate, e.g.

CONCATENATE (NEW_USAGE_CAREB) LOAD ....

for QV it actually does not matter (mmmmh maybe, if there are two identic tables created with NO CONCATENATE ...)

For me it is not quite clear, why you do load the PP_USAGE.QVD several times ... (actually loading from a qvd might be faster than a RESIDENT load, but it makes the script a bit more complicated to read ....