Maintaining uniqueness of surrogate PK generated by Autonumber() when incrementally loadind data

[#1]

My case:

each new day QV-application is loading a portion of data for that (current) day from a database thus adding it to previosly loaded data.

I can't use PK from DB-table, because its's re-generated each day. For that reason I need a way to generate PK inside the app. Autonumber() seems to be a good option, but it will also generate integer-sequnce starting from zero each time the script is loaded.

If Autonumber() had a parameter defining a value to start sequence from it would be a solution (e.g.: "Autonumber(some_field, 'Counter1', 456897)"), but it's not the case.

Do any workarounds exist to perform what's needed in my situation?

[Gysbert_Wassenaar]

Create a key from the PK and the load date.

let vDate = floor(today());

Data:

LOAD * FROM MyHistoricData.qvd (qvd);

Concatenate(Data)

LOAD *, HASH128( PK, $(vDate)) as Key;

SELECT * FROM ... ;

STORE Data INTO MyHistoricData.qvd;

[maxgro]

maybe the start sequence could be the max of MyHistoricData +1

for hash, google for hash collision and read the qlikfix blog

[Oleg_Troyansky]

This maybe a wrong question, but... what do you need the PK for, exactly? If this is your Fact table, loading PK usually doesn't do you any good in terms of analytic value. Unless you need to link to another table with the same key, what else do you need it for?

If you actually do need it for some reason, is it possible to identify the data elements that identify each row of data in a unique way? For example, a combination of Order Number, Line Number and a Date... Then you could use that as a Combo Key...

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

[#5]

I tried what you'd adviced, but it didn't work.

Here's the code:

And the result:

AutonumberHash128() generated identical keys.

Any ideas? Maybe I've done smth wrong?

[Gysbert_Wassenaar]

Use Hash128, not AutoNumberHash128.

[jonathandienst]

Hi

How about this - it should be able to start the autonumber at the right point (although I have not tested this):

// Get last counter value

Load Max(Key) as MaxKey

FROM AllData.qvd;

Let vMaxKey = Peek('MaxKey');

// Set autonumber counter to correct value

// Make values negative so they don't match any actual values...

T_SetAutonumber:

LOAD AutoNumber(RowNo() * -1, 'Counter1') As T_Key

AutoGenerate $(vMaxKey);

// Now use AutoNumber 'Counter1' ...

...

// And clean up

DROP Table T_SetAutonumber;

Set vMaxKey = ;

HTH

Jonathan

[jonathandienst]

See attached

[#9]

Thanks, Gysbert!

Your answer is technically applicable, but in practice I have too many rows of data (tens of millions) to use text PKs.