topic Re: Concatenating two fields, separated by a decimal point (period) in Connectivity & Data Prep

Concatenating two fields, separated by a decimal point (period)

awwitas — Wed, 07 Apr 2021 21:21:31 GMT

Hi,

I'm using Qlik Sense Server Feb 2020.

I have a data set for which I need to generate a key to match another data set. This key should consist of two strings of numbers concatenated together into a text string, separated by a period.

So, if string 1 = '12345' and string2 = '6789', then key should be '12345.6789'

This inline data is a very simplified version of the actual data set, which is much larger and comes from an SQL query.

Load string1 & '.' & string2 as keyA, string1 & '|' & string2 as keyB, string2 & '.' & string1 as keyC, *; Load * inline [ string1, string2 272587940, 2021040421074800 272587940, 2021040512005900 272587940, 2021040516282100 272587940, 2021040517541500 272587940, 2021040518032700 272587940, 2021040523321500 272587940, 2021040605040600 272587940, 2021040606043100 272587940, 2021040611331100 272587940, 2021040611441800 272587940, 2021040616335900 272587940, 2021040616423900 272587940, 2021040700314300 272587940, 2021040700371100 272587940, 2021040710261600 272587940, 2021040710315000 ];

This is the resulting data:

What's going on here? I would expect keyA to be a simple concatenation of string1 and string2 but it clearly is not.

Some observations:

I can see that keyA is being interpreted as a number... but why isn't it a unique number for each row?
Making String1 much longer results in unique text strings in keyA
Making String2 shorter results in unique text strings in keyA
Loading the pre-concatenated string inline (like 272587940.2021040421074800) gives the same result as KeyA above
Loading the pre-concatenated string inline as a string (like '272587940.2021040421074800') gives the same result as KeyA above
using text(string1 & '.' & string2) as keyA results in a 'correct' result for keyA

I have a workaround I can use to make this function (ie pull the concatenated string in SQL as a temporary field and wrap in a text() function using a pre-load statement) but I would really like to know what is happening that makes Qlik give this result.

Thanks in advance!

Re: Concatenating two fields, separated by a decimal point (period)

rwunderlich — Thu, 08 Apr 2021 17:06:24 GMT

You are bumping up against the limits of numeric precision in floating point numbers.

https://community.qlik.com/t5/Qlik-Design-Blog/Rounding-Errors/ba-p/1468808

In your case,

272587940.20210404210748 = 272587940.20210405120059

which is not what you want. The correct solution for your requirement is to treat these as text() values.

-Rob

Re: Concatenating two fields, separated by a decimal point (period)

awwitas — Thu, 08 Apr 2021 17:27:29 GMT

Ahh, I think I understand. This is a result of floating point precision, combined with the dual nature of Qlik data.

Taking these two rows as an example

a) 272587940, 2021040421074800 b) 272587940, 2021040512005900

Row a) will result in a concatenated value that is displayed as 272587940.2021040421074800, but the internal, rounded value that Qlik uses does not truly have that level of precision

Row b) will result in a concatenated value whose internal, rounded value is the same as row a), so Qlik displays it using the same visual representation

Neither row a) or row b)'s concatenated number is actually equal to 272587940.2021040421074800 - that just happens to be the string representation assigned to that number.

Thanks, that's very helpful.

Re: Concatenating two fields, separated by a decimal point (period)

rwunderlich — Thu, 08 Apr 2021 18:35:42 GMT

You stated that very well.

-Rob

Re: Concatenating two fields, separated by a decimal point (period)

awwitas — Thu, 08 Apr 2021 19:10:11 GMT

You can deduce this from the above, but I thought it should be stated explicitly for completeness and for anyone stumbling across this post in the future...

When two values in Qlik have the same numeric representation, Qlik will assign them all the string representation of the first loaded value, regardless of what the string value 'should be'.

For example, doing this:

Load Dual(valA,KeyA) as dualX, Dual(valA,200) as dualY, text(KeyA) as stringA, *; Load * inline [ KeyA, valA 272587940.2021040421074800, a 272587940.2021040512005900, b 272587940.2021040516282100, c 272587940.2021040517541500, d 272587940.2021040518032700, e 272587940.2021040523321500, f 272587940.2021040605040600, g ] ;

Gives you these results, and makes it a little clearer what's happening:

Even though we try to explicitly create a dual() value with the same number but different text, Qlik doesn't allow that and uses the first loaded string value instead ("a" in this case)