Sum-If with Text-Field

[ms-roha]

Hello,

I've found a strange behaviour in Qlik Sense and QlikView and have created a simplified example.

I'm using the following load script:

LOAD 
  Text(Trim(VKORG))								 	as VKORG,
  Dual(Text(Trim(VKORG)), Num(Num#(VKORG))) 		as VKORG_DUAL,
  Year,
  Month
Inline [VKORG, Year, Month
1000,2024,1
1000,2024,2
1000,2024,3

2000,2024,1
2000,2024,2
2000,2024,3](delimiter is ',');

Then I've created 2 simple tables, each containing a Sum(If...) expression like this:

SUM(IF([VKORG] = '1000',
1,
0
)
)

I did this for my Text- and my Dual-field. The result is as follows:

The Sum-IF with the Text field (left side) only works correctly if I use a function like Trim() or WildMatch (or if I use "like" instead of "=")

The Sum-IF with the Dual field (right side) works as I would expect.

The behaviour is the same for Qlik Sense & QlikView (newest versions).

Does anyone know why this is happening? And is there a way to make it work without changing the expression or switching to dual?

Thanks in advance!

[cwolf · Accepted solution]

The operators < > = only work for operands that can be interpreted numerically.

The result of the Text() function is a string for which numerical interpretation is not allowed.

You have to use the comparison operators for strings: precedes, follows, like.

SUM(IF(VKORG like '1000',
 		1,
 		0
     )
)

[ms-roha]

Hello,

thank you I didn't know that.

That makes me wonder why the "="-sign works when I use the Trim()-function, according to the documentation the Trim()-function also returns a string. I guess it allows for a numerical interpretation while the Text()-function removes it completely.

We've decided to replace the Text-Field with a dual and now everthing works as expected.