set analysis - isnull

[lotemki1]

HI all,

I'm trying to understand how can I count B only when A is null. What is the best way, base of performance, and of course, the right calculation.

1. count( {<A={"=Len(Trim([A])) = 0"}>} B)

2. count({<B={"=isnull(A)"}>} B)

3. count({<A={"=isnull(A)"}>} B)

4. Count({1-$<A={"*"}>} B) - if this is the best way, how can I do it with more conditions inside the set analysis ( for example count  B if A is null and (C=1 or D=1)

This is only the options that I saw, if anyone have another way I will be happy to hear.

Thanks!

[Kushal_Chawda]

Best way always is that create a flag in script and use it in set like below

LOAD *,

         if(len(trim(A))=0,1,0) as Null_Flag

FROM table;

then use below in your measure

Count({<Null_Flag={1}>} B) 

[lotemki1]

Hi,
Thanks!
I have a lot of fields that I want to do it.. So, I don’t want to create a lot of flags and prefer to do it in the UI.
So what is the best way to do it – not in script

[Kushal_Chawda · Accepted solution]

Although you have lot of fields creating lot of flag is not a problem actually it is advisable. If you need to improve the performance of an expression, or just make an expression easier to write, you will probably need to create a flag field in the script.

I don't think the first 3 options suggested by you will work.  4th option will definitely work.  If data is huge  then definitely there will have performance impact which you may need to test it out . I am sure script flag method will work better. If you want to use more condition in 4th option then you need to use'+' operator within set like below

Count({1-$<A={"*"}>+<C={1},D={1}>} B)

There is another method within set which you can use it . For that you may need to create unique key in script which you can create it using Rowno() function.

LOAD *,

          RowNo() as Row

FROM source;

Then below set expression

=count({<Row=e({<A={"*"}>})>+<C={1},D={1}>}B)

You may need to test out the performance  of both of these  set options.  I am sure the first set method will be faster than using above but I haven't validated.

[lotemki1]

Thanks a lot for your response!

I will try both of the ways.. How can I test my performance for each option? what is the best and easy way to check performance of my app?

[lotemki1]

Hi,

Another question about your expression:

Count({1-$<A={"*"}>+<C={1},D={1}>} B)

Isn't '+' operator is 'or'?

I wanted to do - if(A is null and C=1) or (A is null and D=1). How can I do it with 1-$ will be only on A and nor C/D in the expression?

Thanks in advance.

[Kushal_Chawda]

You can use document analyzer tool.

[Kushal_Chawda]

You can try below

=Count({1-$<A={"*"}>}aggr(only({<C={1}>+<D={1}>}B),B))