Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
d_pranskus
Contributor III

Creating numeric key fields to connect tables

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

Tags (4)
Community Browser