nested if and sum statement in the Expression

[Anonymous]

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

[Anonymous]

Try this:

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

[richard_chilvers]

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

[Anonymous]

it wont pick up the NULLs.

[Anonymous]

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,

[tamilarasu]

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.?

[Anonymous]

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

[jonathandienst · Accepted solution]

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

[jafari_ervin]

try this:

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