Join between two tables where key field is having 18 digits

[raju_salmon]

Hi Expert,

I have to join two QVD's with a field which is having 18 digits in it. Though it is 1:1 relation the data is getting duplicated.

I understand that it is not considering 18 digits for joining and showing key field info like below.

1.0000000003537E+18

1.0000000003538E+18

1.0000000003539E+18

1.000000000354E+18

Can anyone help how to handle this in Qlik to join?

Regards,

Raju

[sunny_talwar]

Why don't you convert the number to text and then join the two together?

[raju_salmon]

Thank you, it is showing same result though i have converted to text.

[sunny_talwar]

Same result? Can you elaborate on this?

[raju_salmon]

I have used

Load

field,

text(field) as field1 from table;

Both are showing same result like '1234567891234e+18'.

If i user a text box and evaluate the same 

=num(123456789123456789) result is 1.2345678912346e+17

=text(123456789123456789) result is 123456789123456789

If i divide field as two separate field and concatenate

Left(num(field,####################), 15)&Mid(num(field,,####################),16,5) as new field

last two digits are changing

Actual number in DB is 1000000000035242352428599; result in QV is 1000000000035242352428544.

Not sure why last two digits are changing

[marcus_sommer]

Depending on the used number-system Qlik couldn't handle numbers with more as 15 respectively 14 digits as numbers. Therefore the suggestion to convert the numbers to text and if the db or the driver returned the value with a scientific notation it might be useful to make this conversion already within the sql-query.

- Marcus