Qlik Sense over Greetree - Floating Decimals when loading data

[youngi]

Greentree uses Unique Keys in tables, called Object ID’s (OID’s), to join tables together.

As an example, I am joining Branch and Profit Centre tables together via the Branch.OID = 2540.3 (namely “VIC” Branch)

(See attachment for diagrams)

When the data is loaded into Qlik Sense the Branch.OID takes on a whole new format i.e. 0002540.00000000000000000001 (20 decimal places?)  

(See attachment for diagrams)

Which being a Unique Key field on a table, doesn’t retain the 1 - 1 or 1 - Many relationships this key should retain. 

My question is:

Is there a way of forcing numeric values to have a set number of decimals in the code BEFORE the data is loaded?  Rendering the Key Fields functional again?

NB I can manipulate this in Data Load Editor BUT then I am loosing heaps of functionality I need via the Data Manger!

[ToniKautto]

Qlik Indexing engine uses 64 floating point values to represent decimal values, which means all decimal values have 17 digits with 14 digits precision. The decimal values are also not exact by definition, which means that are not suitable to be used for Qlik key fields and join operations. 

I am not sure about the exact numbers in your screenshots, so would need access to a sample app to better understand and explain the exact details in this particular example. 

Common approach in Qlik Sense would  convert the decimal value to integers, for example with construct like Floor(Field * 1000) or Round(Field * 1000). Alternatively, chose a text or integer based field for the join or key, if there is an other field that represents the same relationship in the data. 

[youngi · Accepted solution]

Hi Toni, 

Thanks for the answer and you are 100% correct.  This is definitely a way of resolving the solution but it would mean having to use Data Load Editor and lose the functionality of the Data Manger.   I found a solution that works which entail chainging the .ini file.   Check below.

[JadeODBC]

QueryExecutionTraceOn=false

ServerResultSetBufferSize=100M

SortMemory=50M

QueryTimeout=<default>

OidFieldSeparator=_    ................. (add this field only)

So this will change the numeric keys (with 20 decimals) to alphanumeric which works perfectly.

Thanks for your help.

Cheers

Ian

[ToniKautto]

Great to hear you fond a way forward. I think it sounds like the correct answer for your issues, to convert the key fields to text.