topic Re: Incremental Load of 2 Files in QlikView

Incremental Load of 2 Files

cbaqir — Mon, 18 Sep 2017 20:38:45 GMT

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.

Re: Incremental Load of 2 Files

prieper — Tue, 19 Sep 2017 07:00:46 GMT

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

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

Re: Incremental Load of 2 Files

cbaqir — Tue, 19 Sep 2017 16:02:04 GMT

It's still stating MAX_DATE table not found.

Re: Incremental Load of 2 Files

prieper — Wed, 20 Sep 2017 09:31:08 GMT

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.

Re: Incremental Load of 2 Files

cbaqir — Thu, 21 Sep 2017 18:57:53 GMT

Re: Incremental Load of 2 Files

prieper — Fri, 22 Sep 2017 08:55:22 GMT

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

Re: Incremental Load of 2 Files

cbaqir — Fri, 22 Sep 2017 18:27:27 GMT

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?

Re: Incremental Load of 2 Files

prieper — Mon, 25 Sep 2017 07:11:01 GMT

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

Re: Incremental Load of 2 Files

cbaqir — Mon, 25 Sep 2017 16:04:21 GMT

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?

Re: Incremental Load of 2 Files

prieper — Mon, 25 Sep 2017 16:17:30 GMT

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

Re: Incremental Load of 2 Files

cbaqir — Mon, 25 Sep 2017 16:22:44 GMT

I am trying to append only new data to the QVD. Load the exisiting QVD, find max trx_date, pull new data from first spreadsheet, add to existing QVD, repeat for second spreadsheet. Here is my code:

Edit: the data has loaded without an error but it doesn't look right to me. I am looking at it now.

//3. Load the existing data from QVD file

PP_USAGE:
LOAD date(TRX_DATE, 'mm-dd-yyyy') as TRX_DATE,
// VERSION_PW_CAT_ID,
   // DESCRIPTION,
   FACILITY,
// as FACILITY_DISPLAY,
// FACILITY as ORDER_VV,
   // FACILITY as PLAN_VV,
   // DOMAIN,
   %PP_DOMAIN_Key, //Join to ORD_USAGE
   //autonumber(VERSION_PW_CAT_ID & '|' & DOMAIN) as %PP_DOMAIN_Key, //Join to ORD_USAGE
   CNT_USAGE
//USAGE as CNT_USAGE
   // EXTRACT_DATE
FROM
[..\QVD\01_Extract_QVD\PP_USAGE.qvd]
(qvd);
//[..\External_Data\PP_USAGE_ECISA.xlsx]
//(ooxml, embedded labels, table is CCG_PLANS);

//4. Find max date of QVD file

MAX_DATE:
LOAD
max(TRX_DATE) as Maxdate
resident PP_USAGE;

//5.Store the Maximum date in a variable.

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

Let Maxdate = floor(peek('Maxdate'));

//6. Drop the stored table

//drop table PP_USAGE;

////6. Drop the stored table
drop table MAX_DATE;

//7.Pull the rows that are new from `1DHO_EKG_PLAN_USAGE_CAREB.csv`.

NEW_USAGE_CAREB:
LOAD date(TRX_DATE, 'mm-dd-yyyy') as TRX_DATE,
// VERSION_PW_CAT_ID,
   // DESCRIPTION,
   FACILITY,
// DOMAIN,
   autonumber(VERSION_PW_CAT_ID & '|' & DOMAIN) as %PP_DOMAIN_Key,
// DESCRIPTION & '_' & DOMAIN as %DESC_DOMAIN_Key, //join to Regional Variability
   USAGE as CNT_USAGE
// EXTRACT_DATE
FROM
[..\External_Data\1DHO_EKG_PLAN_USAGE_CAREB.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
where TRX_DATE > $(Maxdate);

//8. Concatenate the new values with existing qvd.

Concatenate
LOAD date(TRX_DATE, 'mm-dd-yyyy') as TRX_DATE,
FACILITY,
%PP_DOMAIN_Key,
CNT_USAGE
FROM
[..\QVD\01_Extract_QVD\PP_USAGE.qvd]
(qvd);

//9. store date into existing qvd
store PP_USAGE into [..\QVD\01_Extract_QVD\PP_USAGE.qvd]
(qvd);

//Pull the rows that are new from `1DHO_EKG_PLAN_USAGE_ECISA.csv`.

//////// Powerplan Usage from QVD //////////
//3. Load the existing data from QVD file

PP_USAGE:
LOAD TRX_DATE,
// VERSION_PW_CAT_ID,
   // DESCRIPTION,
   FACILITY,
// as FACILITY_DISPLAY,
// FACILITY as ORDER_VV,
   // FACILITY as PLAN_VV,
   // DOMAIN,
   %PP_DOMAIN_Key, //Join to ORD_USAGE
   //autonumber(VERSION_PW_CAT_ID & '|' & DOMAIN) as %PP_DOMAIN_Key, //Join to ORD_USAGE
   CNT_USAGE
//USAGE as CNT_USAGE
   // EXTRACT_DATE
FROM
[..\QVD\01_Extract_QVD\PP_USAGE.qvd]
(qvd);
//[..\External_Data\PP_USAGE_ECISA.xlsx]
//(ooxml, embedded labels, table is CCG_PLANS);

//4. Find max date of QVD file

MAX_DATE:
LOAD
max(TRX_DATE) as Maxdate
resident PP_USAGE;

//5.Store the Maximum date in a variable.

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

Let Maxdate = floor(peek('Maxdate'));

//6. Drop the stored table

drop table MAX_DATE ;

//7.Pull the rows that are new from `1DHO_EKG_PLAN_USAGE_ECISA.csv`.

NEW_USAGE_ECISA:
LOAD TRX_DATE,
// VERSION_PW_CAT_ID,
   // DESCRIPTION,
   FACILITY,
// DOMAIN,
   autonumber(VERSION_PW_CAT_ID & '|' & DOMAIN) as %PP_DOMAIN_Key,
// DESCRIPTION & '_' & DOMAIN as %DESC_DOMAIN_Key, //join to Regional Variability
   USAGE as CNT_USAGE
// EXTRACT_DATE
FROM
[..\External_Data\1DHO_EKG_PLAN_USAGE_ECISA.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
where TRX_DATE > $(Maxdate);

//8. Concatenate the new values with existing qvd.

Concatenate
LOAD TRX_DATE,
FACILITY,
%PP_DOMAIN_Key,
CNT_USAGE
FROM
[..\QVD\01_Extract_QVD\PP_USAGE.qvd]
(qvd);

//9. store date into existing qvd
store PP_USAGE into [..\QVD\01_Extract_QVD\PP_USAGE.qvd]
(qvd);

Re: Incremental Load of 2 Files

prieper — Mon, 25 Sep 2017 16:40:30 GMT

You do not need to drop the PP_USAGE.

PP_USAGE: LOAD .... FROM PP_USAGE.QVD;

MAXDATE: ... // ok

LET MaxDate = ..... // ok

DROP TABLE MAXDATE; // ok, cleaning up

CONCATENATE (PP_USAGE) LOAD .... FROM ECISA..... WHERE TRX_DATE > $(MaxDate); // ok, new records appended

STORE PP_USAGE INTO .....

and you are done ....