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

[awwitas]

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!

[rwunderlich · Accepted solution]

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

[awwitas]

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.

[rwunderlich]

You stated that very well.

-Rob

[awwitas]

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)