Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
FabioManniti
Contributor III
Contributor III

IF Condition returns error

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).

Labels (1)
10 Replies
deepanshuSh
Creator III
Creator III

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. 

Trial and error is the key to get unexpected results.
FabioManniti
Contributor III
Contributor III
Author

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
Champion III
Champion III

try below

 

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
FabioManniti
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Champion III
Champion III

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)
)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
FabioManniti
Contributor III
Contributor III
Author

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
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Saurabh_K14999
Contributor III
Contributor III

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