IF Condition returns error

[FabioManniti]

Hello, I have two tables: users and networkcut.

I want to count how many items in users match with the selected networkcut id.

If I don't select any networkcut item it should return 0 (otherwise it would be a too big calculation); so my code is:

IF(GetSelectedCount(netcut_name)=0, 0,
           COUNT(distinct IF(WildMatch(user_hierarchy, '*'&netcut_id&'*')=1, user_id))
)

But still, if I don't select any network item, it still processes everything before returning 0 (and since it is too big, it returns an error).

[deepanshuSh]

I dont think that should be the case, take the netcut_name into a text box and select the filter, also, try to use the two if conditions into two different KPI boxes to understand the processing time difference, because without data model the logic looks pretty straightforward. 

[FabioManniti]

I have checked already, the filter itself works.

What it looks like is QLik calculates both values either way and then, based on IF condition, choses which one to take.

I don't like this I want QLik to do the calculation ONLY if the IF condition is satisfied.

[vinieme12]

try below

IF(GetSelectedCount(netcut_name)=0, 0,
           COUNT({<user_hierarchy={"*netcut_id*"}>}distinct  user_id)
)

[FabioManniti]

Tried but nope.

It returns 0 anyways because, I guess, it considers "netcut_id" as a string and not its value.

I tried changing manually netcut_id with a random value and works; but I should have it dynamically

[FabioManniti]

I have changed like this and it looks like it works

IF(GetSelectedCount(netcut_name)=1,
     Count(DISTINCT {<user_hierarchy = {"$(='*' & netcut_id & '*' )"}>} user_id),
      0
)

I will make some more tests and will let you know

[vinieme12]

as below

IF(GetSelectedCount(netcut_name)=0, 0,
           COUNT(  distinct IF(INDEX(lower(user_hierarchy), lower(netcut_id)), user_id)    )
)

OR

IF(GetSelectedCount(netcut_name)=0, 0,
           COUNT({<user_id ={"=INDEX(lower(user_hierarchy), lower(netcut_id))"} >}distinct  user_id)
)

[FabioManniti]

No, still takes too long in calculation.

So far the best solution is the one you proposed up with my little editing:

IF(GetSelectedCount(netcut_name)=1,
     Count(DISTINCT {<user_hierarchy = {"$(='*' & netcut_id & '*' )"}>} user_id),
      0
)

Thank you very much

[vinieme12]

this won't work, set expression is calculated once per chart not for each row

[Saurabh_K14999]

Hi,

Try using this expression

IF(ISNULL(AGGR( COUNT(DISTINCT IF(WildMatch(user_hierarchy, ''&netcut_id&'')=1, user_id)), netcut_name )), 0, AGGR( COUNT(DISTINCT IF(WildMatch(user_hierarchy, ''&netcut_id&'')=1, user_id)), netcut_name ))

Regards,

SK