topic Re: SQL database load results in a diffrent data outcome in Qlik Automate

SQL database load results in a diffrent data outcome

Marius_ — Wed, 24 Apr 2024 06:34:18 GMT

Using a SQL Query on a SQL database returns the following data:

With the following result:

But executing the same SQL query into Qlikview returns the following:

toetsOnveranderd - Test with no change

toetsText - Using Text script formula to get text property

toetsJippo - Self help method to force test property

With the following outcome in Qlikview:

It seems that the 'raw' data in the SQL database is 0965 (OneID,toetsOnveranderd)

But the SQL query in SQL returns (0965,965) (?)

And it seems that the script formula "Text" first identify the property is integer makes it a number and the concerts it to text 0965 -> 965 -> "965"

[It must be noted that data input is from multiple sources into the SQL database].

This outcome of the usage of the the text formula vs 'raw' data causes a different result/outcome.

Any idea how to "fix" this?

Re: SQL database load results in a diffrent data outcome

BrunPierre — Wed, 24 Apr 2024 10:49:46 GMT

I think "OneID" is a varchar-type field, that's why "0992" and "992" would be treated as different values, because varchar fields are variable-length character strings, and they can store any combination of characters, including leading zeros.

Re: SQL database load results in a diffrent data outcome

Marius_ — Wed, 24 Apr 2024 11:54:28 GMT

Hi BrunPierre

Thanks

I saw that on a SQL site where they described varchar, the use of the length of the field as descriptive purpose was used.

My data is then:

With the outcome that, should any kind of formula (text OR len) be applied to the field, the length and thus the amount of characters will change!

Any idea how to fix/prevent this?

Re: SQL database load results in a diffrent data outcome

BrunPierre — Wed, 24 Apr 2024 13:13:12 GMT

By explicitly converting [OneID] to Text([OneID]), you can ensure that its length remains unchanged as in this specific case they represent two distinct values in the source table.