Creating numeric key fields to connect tables

d_pranskus — Fri, 16 Mar 2012 12:19:03 GMT

Hello

I would like to ask, could someone share some best practices how to create numeric key fields to connect tables in QlikView data model.

If you load full scope of data all at once, this is not a problem - just use AUTONUMBER function.

But if you load incremental data, just the AUTONUMBER function is not enough. I came up with the conclusion, that using APPLYMAP function is not very convienient, because it returns a argument if it does not find a corresponding record in mapping table.

Now I came up with this approach

1. Maintain a table for each key which contains two fields - key field expression and row number.

2. Fill the table with new key field expression combinations as needed.

3. Load it before all transformations with LOAD AUTONUMBER(key_field, 'map_field_name') as field FROM .... (qvd). This is needed to setup AUTONUMBER function counter

4. Use AUTONUMBER(key_field, 'map_field_name') everytime you need a key value.

This works quite fine. But maybe there is some better solution.

Thank you very much

Cheers

Darius

Hello

I would like to ask, could someone share some best practices how to create numeric key fields to connect tables in QlikView data model.

If you load full scope of data all at once, this is not a problem - just use AUTONUMBER function.

But if you load incremental data, just the AUTONUMBER function is not enough. I came up with the conclusion, that using APPLYMAP function is not very convenient, because it returns an argument if it does not find a corresponding record in mapping table.

Now I came up with this approach

1. Maintain a table for each key which contains two fields - key field expression and row number.

2. Fill the table with new key field expression combinations as needed.

3. Load it before all transformations with LOAD AUTONUMBER(key_field, 'map_field_name') as field FROM .... (qvd). This is needed to setup AUTONUMBER function counter

4. Use AUTONUMBER(key_field, 'map_field_name') every time you need a key value.

This works quite fine. But maybe there is some better

topic Creating numeric key fields to connect tables in QlikView

Creating numeric key fields to connect tables