Incremental Load Issue

[Anonymous]

I thought I had my incremental load working fine but I am having an issue using the "where not exists" function. It appears that the call isn't returning all the data I expect.

I have a table in memory called PPTemp. This contains the current day's data, including changes to already reported data. I.e.

%Key	Synd_Line_No	Amount
20120101_12345_1	1	1000
20120101_12345_1	2	1000
20120101_12345_1	3	1000
20120101_12345_1	4	1000
20120101_12345_1	5	1000

I then have a QVD file containing all data processed on previous days:

%Key	Synd_Line_No	Amount
20120101_12345_1	1	500
20120101_12345_1	2	500
20120101_12345_1	3	500
20120101_12345_1	4	500
20120101_12345_1	5	500
20120101_67890_1	1	2000
20120101_67890_1	2	2000
20120101_67890_1	3	2000
20120101_67890_1	4	2000
20120101_67890_1	5	2000
20120131_54321_1	1	5000
20120131_54321_1	2	5000
20120131_54321_1	3	5000
20120131_54321_1	4	5000
20120131_54321_1	5	5000

So as you can see, the data I am loading transaction 20120101_12345_1 again, because the amount has changed.

I have the following script:

Concatenate (PPTemp) LOAD *

                    From $(vDriveLetter)$(vOutputQvd)$(vFileName)(qvd)

                    Where not Exists(%Key);

I would expect 10 rows to be concatenated onto PPTemp from the QVD file, but I am only getting 2. I am each distinct %Key that doesn't exist, but only one Syndicate Line of data for each.

Has anyone seen this problem before, and can you suggest a way to go about fixing it?

Many thanks

[rwunderlich]

The problem arises because the not exists on a single field picks up only one row for each key from the QVD. The workaround is to copy the delta keys to a new field and use that in the exists. For example:

keys_temp:

LOAD %Key as %tempKey RESIDENT PPTemp;

Concatenate (PPTemp) LOAD *

From $(vDriveLetter)$(vOutputQvd)$(vFileName)(qvd)

Where not Exists(%tempKey, %Key);

DROP TABLE keys_temp;

Or use Qlikview Components http://qlikviewcomponents.org like this:

SET Qvc.Loader.v.KeyFieldIsUnique = 0;

CALL Qvc.UpdateQvd ('PPTemp', '$(vDriveLetter)$(vOutputQvd)$(vFileName)', '%Key');

-Rob

http://robwunderlich.com

[Anonymous]

Many thanks, I'll give this a try next time. As I was up against it somewhat, I reversed the logic for this exercise.

I.e.

- Stored the working data into a temporary QVD

- Created a unique list of 'old' keys that hadn't been worked on

- Loaded from the QVDs where these 'old' keys exist

- Concatenated the working data back in again

Seems to be working fine and I am not losing any of the optimisation.