topic Re: Incremental Load without a Unique ID in QlikView

Incremental Load without a Unique ID

rustyfishbones — Mon, 29 Feb 2016 13:20:47 GMT

Hi All,

I have an issue where the primary key is made up of 4 fields

Warehouse,TrxType,TrxNo and LineNumber

What is the fastest way to get the new records from the DB, If I concatenate the fields and create a unique field. when I attempt to load the new records from the DB it will be very slow as the concatenated fields are not Indexed.

Questions:

Can I create an Index on the SQL Query with the 4 fields.

Can I use AutoNumberHash128 and will it find the new records faster.

I need to Join this table with another table from another DB, the other DB has a Unique Sequence No that's also Indexed and I can get the new records really fast.

What is the best way to join these 2 tables and ensure I can do it quickly.

Thanks

Regards

Alan

Re: Incremental Load without a Unique ID

jonathandienst — Mon, 29 Feb 2016 13:35:45 GMT

The problem is that an incremental load is only useful if there is some way of only loading from the database the new or recently changed records. This can work with a date field (eg date_added, date_updated), or a monotonically increasing key value.

So your composite key uniquely identifies the records, but it may prove tricky unless the TrxNo increases steadily and/or you have a small number of warehouses.

Re: Incremental Load without a Unique ID

jonathandienst — Mon, 29 Feb 2016 13:37:03 GMT

>>Can I create an Index on the SQL Query with the 4 fields\

You probably should, but this is a question that should go to your db administrators.

Re: Incremental Load without a Unique ID

jonathandienst — Mon, 29 Feb 2016 13:40:01 GMT

>>Can I use AutoNumberHash128 and will it find the new records faster.

I don't see how this would help you. The Autonumbered value exists in QV, and AutonumberHashxx values have no ordinality that you could use in the SQL where clause. My first post above attempts to explain that.