topic Incremental load only loading unqualified fields in QlikView

Incremental load only loading unqualified fields

Fri, 13 May 2011 14:50:17 GMT

I am trying to run an incremental load with some SQL and data stored in a QVD file. For some reason it is loading the QVD file, but only the unqualified fields. I tried running it for just the QVD file (without the SQL) and it did the same thing. Any ideas on what I am missing?

ALL_DATA:

qualify *;

unqualify s_code

, d_date;

LOAD *;

SQL

select s_code

, d_date

, r_date

, mc_code

, d_cnt

, r_cnt

from TABLE

where date(s_date) >= '$(DATE_START)'

and date(s_date) < '$(DATE_CURR)';

CONCATENATE (ALL_DATA) LOAD * FROM ALL_DATA.QVD (QVD) WHERE s_date < '$(DATE_START)';

Incremental load only loading unqualified fields

Fri, 13 May 2011 15:31:35 GMT

I've been messing around with it some more, and it looks like the qualification on the QVD part is putting the table name twice. So when I run it normally (without incremental load) with just the SQL I get ALL_DATA.r_cnt, but when I comment out the SQL and just run the QVD portion I get ALL_DATA.ALL_DATA.r_cnt. So it is pulling the fields in, but the qualification is getting messed up. Not sure how to fix that...

Incremental load only loading unqualified fields

Fri, 13 May 2011 15:47:23 GMT

I think I just figured it out... If I put

unqualify *;

in between the concatenate and the load it is qualifying with only 1 table name.

Re: Incremental load only loading unqualified fields

fernandotoledo — Fri, 13 May 2011 16:11:12 GMT

Incremental load needs concatenating the historic data from a QVD with the new data from the Database.

But the "Qualify *;" command doesn't allow you to concatenate the tables!

You must make the incremental load to create a temporary Logical Table, then load resident it with the qualify command.

Something like:

TMP_TABLE:

//HISTORICAL DATA

LOAD * FROM HISTORIC.QVD WHERE DATE<$(DATE_CUT);

//NEW DATA AUTOMATICALLY CONCATENATED

LOAD *; SQL SELECT * FROM CURRENT_DATA WHERE DATE>=$(DATE_CUT);

//TURNS QUALIFY ON

QUALIFY *;

//QUALIFIES THE TABLE

TABLE:

LOAD * RESIDENT TMP_TABLE;

//PUTS AWAY THE TEMPORARY TABLE

DROP TABLE TMP_TABLE;

Hope it Helped!

Regards

Fernando

Incremental load only loading unqualified fields

Thu, 02 Jun 2011 14:10:13 GMT

Yep, that worked. For each table I have, I did a separate load to a temp table with everything unqualified and stored that as a QVD. Then I loaded them from the QVDs into QV with the fields I need qualified. The incremental load will add rows to the unqualified QVD files in the same manner.

One hiccup I had was that I had each table on a separate tab in the load script and didn't put an unqualify command for each table, so my qualify statements were carrying over into the other tabs. So you just need to make sure you are putting unqualify *; for each table.

Incremental load only loading unqualified fields

fernandotoledo — Thu, 02 Jun 2011 14:16:25 GMT

Yep, never forget the UNQUALIFY * after the commands