Concatenating fields in script produce inconsistent results

[LynLuyt]

Hi All

I hope someone can shed some light on this problem.

My script runs daily and adds records to a table if they don't exist in the QVD created the day before. The key that I'm using to check that the record doesn't exist, is made up of 2 fields concatenated with a '|' delimiter between them.

I thought everything was working but discovered I had duplicates in my table. I though it may be the WHERE clause as I have multiple conditions, including a WHERE EXISTS as well as an AND NOT EXISTS. However, when putting checks into the code, I discovered that when concatenating these fields, I'm getting inconsistent results. It only happens on a small percentage of the records but it differs every time I run it using exactly the same data. Sometimes it's 70 records out of 3.5 million, sometimes is 0 records and sometimes it's over 400.

The 2 fields are made up of 1 which is a datatype INT (client code) in SQL and the other is varchar(16) (account number). The account number on it's own is not unique and therefore needs to be joined to the client code. Most of the time, the account number contains numbers. I am not converting them to anything.  Sometimes the 2nd field is prefixed with a zero even though in the SQL database there is no zero. If I run the code again, the same number could end up being prefixed by 2 zeros etc. or 3...

Because of this, I am getting duplicate records on my table.

I have attached a spreadsheet with the results. Unfortunately I don't think attaching a QVW will work as the problem lies between pulling the data from SQL and combining it with an existing QVD.

Many Thanks in advance

[sunny_talwar]

@LynLuyt Can you share the code that you use to create this key?

[LynLuyt]

Hi Sunny

Thank you for your interest to help me. I can't give you a QVW as it gets the data from SQL, but I'm attached a Word document with the bit of code that I use. I hope it helps.

[marcus_sommer]

Qlik doesn't have the concept of data-types else the incoming data will be interpreted - as numbers or as strings. These interpretation could be partly controlled through the interpret-variables at the beginning of the script. For everything else defined the first loaded field-value how all values of the field are interpreted - and here is really means the first occurrence within the script.

In most scenarios this simplified approach worked very smoothly. If not you may apply specially created pre-loads to define the wanted interpretation or you apply certain formatting/converting-functions like num(), num#(), text() or even more advanced logics - directly within the load.

In your case I could imagine that:

text(szAccountNumber) as szAccountNumber

may work. Important is that it's applied at the first loading - within your resident load it's too late.

- Marcus

[LynLuyt]

Thank you Marcus

So if I did a preload of just a few records and defined it like this text(szAccountNumber) as szAccountNumber and then dropped the table (or dropped it at the end), would that take care of all the loads in the script, after that as it still has it in memory?  

And what do you mean by the more advanced logics?

Sorry Marcus - what is still confusing me is why it would add leading zeros to a field that doesn't have that in the database - sometimes 1, sometimes 2 etc. ?

[marcus_sommer · Accepted solution]

In regard to the mentioned pre-load take a look on this post which explained it much better as me:

https://qlikviewcookbook.com/2016/07/touchless-formatting/ 

With more advanced logics was meant the use of multiple functions like left/right/mid or subfield() or textbetween() to pick certain parts of the value and/or the use of keepchar() / purgechar() to remove any unwanted content and then to apply any formatting/converting-functions ... In your case it's probably not necessary or helpful but in general has Qlik here very powerful features.

AFAIK leading zeros happens only if there they really exists. Sometimes it could be quite difficult to detect it because the views within the database and also in Qlik may not always show the stored value. Further like mentioned the first loaded field-value determines the result ... 

- Marcus

[LynLuyt]

Thank you Marcus - I appreciate your help.

I read the Cookbook article and I see it doesn't work for text unfortunately. 

I just wanted to ask you if you know of a way I can now fix the data that is in the QVD. So some values that are too big come out as a decimal number. See the example below. Is there someway I can try to get it back to the number it was before it converted it like this?