Table keys joining data differently between QlikView and Qlik Sense

[kdmarkee221]

While converting an app from QlikView to Qlik Sense, I ran into an issue comparing data where values in a field appear to have been joining incorrectly between tables but in Qlik Sense appears to be behaving correctly.  I am joining 2 tables where they each have an MRN field that becomes the joining key in my data model.  This field is a varchar2 in Oracle as it can store numbers or alphanumeric values.  

The results below are from an Oracle query, shown here for simplicity to explain the following...

QlikView actually makes a link on MRN for both  Table 1 rows but it should not, it should only link to the first row.  (The second row has more leading zeros and should not be considered a match via the key).  Qlik Sense actually makes the correct linkage, that is, it only links to the first row.

I'm trying to implement Text() in Qlik View to fix it but it is not working.  Thoughts on (1) Why is QlikView and Qlik Sense behaving differently and (2) How can I fix QlikView's incorrect behavior? Thanks.

[Or · Accepted solution]

If you apply text(Field) on both sides of the key during the original load, you shouldn't be getting a join based on leading zeroes. Note that this needs to be done when you're loading, because if you do it later, Qlik might have already lost the leading zeroes when evaluating as a number.

As to why QV and QS are behaving differently, no idea. Perhaps something to do with how the ODBC works or how they decide to parse a field as numeric if all values in it can be evaluated as numbers, but that's just guessing.

[kdmarkee221]

Yes,  I think I just discovered that as well.  I get correct results if I use Text() during the loading from my Oracle tables, but if I do it later in my transformation script, that is where if falls apart.  FYI... building a joining key with Autonumberhash256 involving this field did not need Text() in order for those keys to work properly.