topic Re: How to create incremental load using 2 fields ? in App Development

How to create incremental load using 2 fields ?

Xabinav — Mon, 18 Mar 2024 12:08:39 GMT

I trying to write script of an incremental load using 2 fields (VehicleID , RowID).

I would appreciate your help in writing the code for the process of incremental loading.

I want to take each time the new Data (Increment), which does not appear in the existing table, and add it to the existing data (Initial Data) and update if data was changed.

Thanks.

Initial Data:

VehicleID	RowID
12345	1
12345	2
12345	3
21212	1

Increment:

VehicleID	RowID
12345	4
21212	2
34343	1

Re: How to create incremental load using 2 fields ?

rwunderlich — Tue, 19 Mar 2024 05:13:28 GMT

Make a composite key field and use that field as your Where not Exists(Key) clause.

VehicleID & '|' & RowID as Key

-Rob

Re: How to create incremental load using 2 fields ?

Xabinav — Tue, 19 Mar 2024 12:02:24 GMT

Thanks Rob.

'Where not Exists' clause doesn't work well in this case because I read data from SQL (SQL SELECT ... FROM)

I have tried to write it like this, It works but doesn't seem to shorten the reload time.

There is another way to write it ?

Source: LOAD VehicleID&'|'&RowID as Key_Source, * FROM [..\QVDs\Source.qvd](qvd); Concatenate(Source) LOAD VehicleID&'|'&RowID as Key, * Where Not Exists (Key_Source, VehicleID&'|'&RowID); DROP FIELD Key_Source SQL SELECT * FROM DB.dbo.SQL;

Re: How to create incremental load using 2 fields ?

rwunderlich — Tue, 19 Mar 2024 14:26:17 GMT

The DROP FIELD should come after the SQL Select.

Concatenate(Source) LOAD * Where Not Exists (Key_Source, VehicleID&'|'&RowID) ; SQL SELECT * FROM DB.dbo.SQL; DROP FIELD Key_Source