Set Analysis

[LoKi_asterix]

Hey everyone!

I’m looking to convert a Sum(If) expression into a set analysis format.

Sum(
If(IsNull( %TransCode ) or Len(Trim( %TransCode )) = 0
or
(Len(Trim( %TransCode )) > 0 and (IsNull([Transaction Type]) or Len(Trim([Transaction Type])) = 0)),
$TransAmtLocal
)
)

[adilio_silva]

I think something like:

{<
  	%TransCode 		= {'*'}
, 	[Transaction Type] 	= {'*'}
>}
Sum($TransAmtLocal)

[howdash]

What I tend to do in these situations is transfer that IF statement to the script and create a new field in the data model, like this:

tableName:
LOAD *,
    If(IsNull( %TransCode ) or Len(Trim( %TransCode )) = 0 or (Len(Trim( %TransCode )) > 0 and (IsNull([Transaction Type]) or Len(Trim([Transaction Type])) = 0)), 1, 0) as forTransAmtLocal
Resident originalTable
;

Once you have this new forTransAmtLocal field (or whatever you want to call it), your expression can be as simple as this:

Sum({<forTransAmtLocal = {1}>} $TransAmtLocal)

[BrunPierre · Accepted solution]

I get that you want where the transaction code or transaction type is missing, then perhaps

=Sum({1-$<%TransCode = {'*'}> + 1-$<[Transaction Type] = {'*'}>} $TransAmtLocal)