topic Where condition in script retrieving incorrect result. in Connectivity & Data Prep

Where condition in script retrieving incorrect result.

M_B — Mon, 20 Jan 2025 11:53:38 GMT

Hello, I am not sure if this is the correct category to post this but here is the issue:

I might have stumbled upon a bug, if not I would definitely like some help with this.

I have the following where condition in script:

Where Len(Trim(CODE)) > 3

Now, even though it says larger than 3, the result will always include 1 and exclude 2 and 3 and start from 4 which is the desired result minus the 1 appearing. Even if I try "Where not Len(Trim(CODE)) < 3", it will still include 1 and exclude 2 and 3, then start from 4.

I have also tried "Where Len(Trim(Text(CODE))) > 3" but it does not make a difference.

Why is 1 always showing up?

Your assistance is appreciated.

Re: Where condition in script retrieving incorrect result.

Mathias_Mols — Mon, 20 Jan 2025 13:10:49 GMT

Hi M_B,

There shouldn't be anything wrong with your where-clause. Would it be possible that you apply the where-clause on a unwanted load-statement which cause then a not expected result?

Kind regards,

Mathias

Re: Where condition in script retrieving incorrect result.

M_B — Tue, 21 Jan 2025 05:26:27 GMT

Hello Mathias,

There is only 1 load statement reading from a QVD.

Re: Where condition in script retrieving incorrect result.

Bhushan_Mahajan — Tue, 21 Jan 2025 05:54:31 GMT

@M_B Please check whether entries in code column are numerical or textual only.

Re: Where condition in script retrieving incorrect result.

M_B — Tue, 21 Jan 2025 13:16:35 GMT

@Bhushan_Mahajan The codes are a mix of pure numbers and some mixed with with characters.

I have just tried to cast the column as text, reloaded the data and stored it into the QVD rewriting it entirely. I reloaded the QVD in the app but the column still does not show any tags when viewing it with the "Data model viewer".

As you can see in the picture, these codes always show up no matter how I write the where clause.

Re: Where condition in script retrieving incorrect result.

marcus_sommer — Tue, 21 Jan 2025 14:48:58 GMT

It seems that you query the wrong column because CODE <> Code.

Re: Where condition in script retrieving incorrect result.

M_B — Wed, 22 Jan 2025 06:09:46 GMT

@marcus_sommer the column is just aliased/labeled in the chart. As you can see, the condition is showing a length of 1. What does not show are 2 and 3, then 4 and larger are shown.

Code	Code Length
#	1
#	1
#	1
#	1
####	4
####	4
####	4
#####	5
######	6

The table represents the results I am getting.

Re: Where condition in script retrieving incorrect result.

marcus_sommer — Wed, 22 Jan 2025 06:32:14 GMT

I never run into an unexpected behaviour of the functionality of functions like: len(trim()). If they don't return the expected values it's usually the result of unexpected values within the field-values.

Therefore it's necessary to check them first, for example with additionally functions, like: num/text/isnum/istext/len as well as recno/rowno and to display the result in a table-box. Maybe the cause becomes obvious and if not it would be helpful to export the object as txt-file (not Excel) and then to open it with an editor like Notepad++. The aim is to see all existing chars and not only the visible ones.

Re: Where condition in script retrieving incorrect result.

M_B — Wed, 29 Jan 2025 07:16:02 GMT

Hello,

Just to update this, I managed to fix the result. It was a logical error. There were two other conditions with an "or" that I isolated with parenthesis that were causing this behavior.

Where Len(Trim(CODE)) > 3 and (condition1 and condition2) or (condition1.1 and condition 2.1) and condtion3

changed to

Where Len(Trim(CODE)) > 3 and ((condition1 and condition2) or (condition1.1 and condition 2.1)) and condtion3