topic Re: an If clause with weird behavior in QlikView

an If clause with weird behavior

max_potass — Fri, 04 May 2018 11:50:54 GMT

Note: I found a way, that I do not have to use an If clause there, so I do not need an answer anymore, but I am still interested, why this did not work.

Hello everyone,

I currently am realy confused with an if clause... I try the following:

If(Not x=y,a,b)

If x=y I actually get b. But when x is not y I do not get a.

How is this possible?

In actuallity x is a max value of a date, y is a variable (also date). a is another expression in the same chart an b is a bit complicated but actually works. It is not nice to look at but here you go:

If(Not Max({<MMM.YY = ,year = ,Type={'Ist'}>}MMM.YY)=PMM_Date_auto,

[AE Ist],

SUM({<MMM.YY = ,year = >}If(MMM.YY=MonthStart(Today(0)),AE.Netto*UKURS))

/NetWorkDays(MonthStart(Today(0)),MAX({<MMM.YY = ,year = >}True_Date),$(vPublicHolidays))

*NetWorkDays(MonthStart(Today(0)),MonthEnd(Today(0)),$(vPublicHolidays))

+SUM({<MMM.YY = ,year = >}If(MMM.YY<MonthStart(Today(0)) And MMM.YY>=YearStart(Today(0)),AE.Netto*UKURS)))

Has anyone an idea what the problem could be?

Re: an If clause with weird behavior

sunny_talwar — Fri, 04 May 2018 11:52:53 GMT

In my simple test, it seems to be working

Re: an If clause with weird behavior

Peter_Cammaert — Fri, 04 May 2018 11:54:01 GMT

One reason could be that you cannot compare anything to a NULL value.

Re: an If clause with weird behavior

sunny_talwar — Fri, 04 May 2018 11:55:01 GMT

Why don't you just do this If(x=y, 'b', 'a')

If(Max({<MMM.YY, year, Type = {'Ist'}>} MMM.YY) = PMM_Date_auto,

Sum({<MMM.YY, year>} If(MMM.YY = MonthStart(Today(0)), AE.Netto*UKURS))

/NetWorkDays(MonthStart(Today(0)),MAX({<MMM.YY = ,year = >}True_Date),$(vPublicHolidays))

*NetWorkDays(MonthStart(Today(0)),MonthEnd(Today(0)),$(vPublicHolidays))

+SUM({<MMM.YY = ,year = >}If(MMM.YY<MonthStart(Today(0)) And MMM.YY>=YearStart(Today(0)),AE.Netto*UKURS)),

[AE Ist])

Re: an If clause with weird behavior

max_potass — Fri, 04 May 2018 11:57:28 GMT

I have to change that in several expressions and with the not it actually is faster to paste. Thanks for the fast response.

Re: an If clause with weird behavior

marcus_sommer — Fri, 04 May 2018 11:57:31 GMT

I think the reason is that there is no aggregation by [AE Ist] and if there are more than value available it will return NULL. Therefore try if min/max/avg([AE Ist]) change it (and return the right results).

- Marcus

Re: an If clause with weird behavior

max_potass — Fri, 04 May 2018 11:58:54 GMT

So if an error occurs, it breaks the if. I see.

But it actually cannot be a null value, otherwise the whole expression cannot work.

Re: an If clause with weird behavior

max_potass — Fri, 04 May 2018 12:00:05 GMT

There is only one Vaule in [AE Ist]. If i just use it as expression it works.

Re: an If clause with weird behavior

max_potass — Fri, 04 May 2018 12:00:35 GMT

I know... I tested it, too.

Re: an If clause with weird behavior

max_potass — Fri, 04 May 2018 12:01:19 GMT

I found a way, that I do not have to use an If clause there, so I do not need an answer anymore, but I am still interested, why this did not work.

Re: an If clause with weird behavior

sunny_talwar — Fri, 04 May 2018 12:01:57 GMT

So, then the if statement is not the problem... it is something else... would you be able to share a sample where this isn't working?