topic Incremental loading problem in QlikView

Incremental loading problem

Tue, 05 Jul 2011 14:11:51 GMT

hi,

i"m trying to load from DB all the rows that were updated using a "LAST_UPDATE_TIME" field in the DB.

All the other rows I get from QVD.

i"m using the following function:

SUB concatenateAndStoreField (table_name,field_name)

IF $(v_load_all_from_db) = 0

THEN concatenate ($(table_name) )

LOAD * FROM $(table_name).QVD (QVD)

WHERE NOT Exists ($(field_name)) ;

END IF

Store $(table_name) into $(table_name).QVD;

The problem is that if the field_name is not a key in the query(it means that it can appear more than once),

The “NOT Exist” damage the QVD and removes all the rows except the first one.

Is there any idea how to use the incremental loading differently ?

Thanks,

Avishay.

Incremental loading problem

swuehl — Tue, 05 Jul 2011 14:51:05 GMT

Hi,

I think you should use "where not exists" only on fields with unique values.

If field_name is not such a field, you can generate one (for example by combination of fields or artificial row number).

There is a good example of incremental loading in the QlikView cookbook (http://robwunderlich.com/downloads/)

Regards,

Stefan

Re: Incremental loading problem

Miguel_Angel_Baeyens — Tue, 05 Jul 2011 17:18:46 GMT

Hello Avishay,

I agree with Stefan in both using the EXISTS() with unique value fields and using the QlikView Cookbook.

Taking a look at your script, there are several things I can think of

First is that you might not need a loop to load from several tables, you can do a

TABLE:
LOAD *
FROM *.QVD (QVD);

Second is that you can load all values you want to keep (or get rid of) in a table and loop that table (or use a mapping table, or the exists clause) in the WHERE part to load only those records. The problem here is how do you identify uniquely those records you want to load. There are function such as Max() or FirstSortedValue() that usually return one value depending on the GROUP BY part, but if the values are not unique, you need something else.

Post some sample data to see how can we help further.

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica