topic Re: Join between two tables where key field is having 18 digits in QlikView

Join between two tables where key field is having 18 digits

raju_salmon — Sat, 16 Nov 2024 04:04:35 GMT

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

Re: Join between two tables where key field is having 18 digits

sunny_talwar — Fri, 22 Mar 2019 10:58:16 GMT

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

Re: Join between two tables where key field is having 18 digits

raju_salmon — Fri, 22 Mar 2019 12:04:50 GMT

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

Re: Join between two tables where key field is having 18 digits

sunny_talwar — Fri, 22 Mar 2019 12:07:57 GMT

Same result? Can you elaborate on this?

Re: Join between two tables where key field is having 18 digits

raju_salmon — Fri, 22 Mar 2019 12:21:52 GMT

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

Re: Join between two tables where key field is having 18 digits

marcus_sommer — Fri, 22 Mar 2019 15:49:11 GMT

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