topic Re: nested if and sum statement in the Expression in QlikView

nested if and sum statement in the Expression

Anonymous — Mon, 09 Nov 2015 08:19:28 GMT

Hi,

I am trying to calculate the following but cant get it to work.

I'm trying to say that if field A = 'ContractRef' and SUPStatus field is not 1,4,8, then sum the SUP field otherwise its 0. I also need to include NULLS.

This is what I have tried (any many more things) so far:-

=If( [ConType]='ContractRef' AND [SUPStatus] = 1) OR [ConType]='ContractRef' AND [SUPStatus] = 4) OR

[ConType]='ContractRef' AND [SUPStatus] = 8),0,sum([SUP#])

any help appreciated. also if you have examples that can help me learn to write these scripts better please let me know.

Cheers,

Laura

Re: nested if and sum statement in the Expression

Anonymous — Mon, 09 Nov 2015 08:24:43 GMT

Try this:

IF(field A = 'ContractRef' and SUPStatus <>1 or SUPStatus <>4,or SUPStatus <>8 ,sum(SUP))

Re: nested if and sum statement in the Expression

richard_chilvers — Mon, 09 Nov 2015 08:43:47 GMT

You might want to check out the MATCH() and MIXMATCH() functions

Re: nested if and sum statement in the Expression

Anonymous — Mon, 09 Nov 2015 08:46:11 GMT

it wont pick up the NULLs.

Re: nested if and sum statement in the Expression

Anonymous — Mon, 09 Nov 2015 08:59:03 GMT

I suggest using a MAP to replace the NULLs with 0 and then use ApplyMap to replace NULL values of SUP with 0. Your SUM() should be able to take care of the NULLs in this case.

ZeroMap:

MAPPING LOAD *inline [

null(), '0'

NULL,'0'

];

....

LOAD

Num(ApplyMap('ZeroMap',SUP),'#,##0.00;(#,##0.00)') as SUP,

Re: nested if and sum statement in the Expression

tamilarasu — Mon, 09 Nov 2015 09:30:55 GMT

Hi Laura,

Try the below one,

If([ConType]=''ContractRef' and Not Match([SUPStatus],1,4,8),sum([SUP#],0)

Not sure, in which field you want to add null. Could you elaborate.?

Re: nested if and sum statement in the Expression

Anonymous — Mon, 09 Nov 2015 09:34:59 GMT

Its obvious, it wont pick, if you want:

You can do like this:

IF(field A = 'ContractRef' and fieldA=null() and SUPStatus <>1 or SUPStatus <>4,or SUPStatus <>8,or SUPStatus=null()

,sum(SUP))

You can rectify it as per your requirement...

Re: nested if and sum statement in the Expression

jonathandienst — Mon, 09 Nov 2015 09:41:04 GMT

This should work

=Sum(If((ConType = ContractRef Or IsNull(ConType)) And (Match(SUPStatus, 1, 4, 😎 = 0 Or IsNull(SUPStatus)), [SUP#]))

Set analysis usually performs much better than a sum(if()) structure, but it is not so easy to include nulls (just as it is not possible to select nulls in the front end). So i suggest that you post a sample qvw file for more detailed analysis. Or you can set a flag at load time and use that in a set expression. Something like:

LOAD

...

...If((ConType = ContractRef Or IsNull(ConType)) And (Match(SUPStatus, 1, 4, 😎 = 0 Or IsNull(SUPStatus)), 1, 0) As Flag1,

...

Then use

=Sum({<Flag1 = {1}>} [SUP#])

Re: nested if and sum statement in the Expression

jafari_ervin — Mon, 09 Nov 2015 09:55:15 GMT

try this:

sum(if([ConType]=''ContractRef' and Match([SUPStatus],1,4,8)=0,SUP#))