topic Re: set analysis - isnull in App Development

set analysis - isnull

lotemki1 — Sat, 16 Nov 2024 18:11:05 GMT

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!

Re: set analysis - isnull

Kushal_Chawda — Wed, 26 Aug 2020 16:00:00 GMT

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)

Re: set analysis - isnull

lotemki1 — Wed, 26 Aug 2020 16:12:52 GMT

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

Re: set analysis - isnull

Kushal_Chawda — Wed, 26 Aug 2020 21:38:45 GMT

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.

Re: set analysis - isnull

lotemki1 — Thu, 27 Aug 2020 07:31:54 GMT

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?

Re: set analysis - isnull

lotemki1 — Sun, 30 Aug 2020 07:04:55 GMT

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.

Re: set analysis - isnull

Kushal_Chawda — Sun, 30 Aug 2020 19:03:31 GMT

You can use document analyzer tool.

Re: set analysis - isnull

Kushal_Chawda — Wed, 02 Sep 2020 22:05:07 GMT

You can try below

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