isnull and not isnull in Set Analysis

[#1]

Hi guys,

I don't have any ideas how to write isnull and not is null in expression using Set Analsyis. In attached example, I have represented how it looks using if function, but I  have to write it using set analysis.  The task is to calculate amount fot that records where FirstValue hasn't value but SecondValue has it without anything changing in the script.

Thanks for help.

[swuehl · Accepted solution]

Try

=sum({$<FirstValue = {'*'} > - < SecondValue = {'*'}>} Amount)

[#3]

Hi,

Try,

Rebeca

[#4]

Thanks, in that case it works, but I have one misundertanding. For example, If I relate this table1 with table2 which contains discription of that 'Dim' field and use insteed of it then it shows strange result (0 values in 'With Set Analysis' column). Please see new attached file.

[swuehl]

Yes, looks kind of strange.

Alternatively, you can create a RecID field in your script and do something like:

sum({$<RecID = p({<FirstValue = {'*'} >}) - p({<SecondValue = {'*'}>}) >} Counter)

[#6]

But unfortunately I don't have opportunity change anything in the app. script.