topic Sum-If with Text-Field in App Development

Sum-If with Text-Field

ms-roha — Tue, 20 Aug 2024 13:50:14 GMT

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!

Re: Sum-If with Text-Field

cwolf — Tue, 20 Aug 2024 21:27:37 GMT

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 ) )

Re: Sum-If with Text-Field

ms-roha — Wed, 21 Aug 2024 08:04:03 GMT

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.