Conditional expressions (without if)

[QuentinBmt]

Hi all,

I was having some big performance issues lately, and I just found out that it comes from the if() function.
After navigating on the Qlik Community Forum, I discovered that this If() function is going through all the branches before evaluating the condition.
One of my expressions being "long" to load, I wanted it to load only when there was at least one filter applied on the page. Here is my current condition :

if( len( GetCurrentSelections() ) > 0
    , Big Expression
    , Small Expression
)

This expression is "long" to load if I do not have any filter applied. Once I apply a filter, it is loading in a decent speed. This is because of the particularity of the if function, the "Small Expression" being almost instantaneous.

I am using this condition in several tab of a container, and my tables take an eternity to load.
I tried several options I found online, to check if there was any improvements :

1. Hide the tabs when the above condition is not met, but it does not change the calculation time (the labels seems to be still loaded).
2. Replace the If() by a Pick() function (no performance gained)
3. Put the if in a variable (within the script) with the expressions as Strings (Partialy solves the issue; The if() is only recalculated when the script is reloaded, and it is not responsive to user manipulations)

I am currently blocked on this, any help would be much appreciated.

Thanks in advance!

[marcus_sommer]

Most important for a good UI performance is a well designed data-model - usually in the direction of a star-scheme in which all relevant measures/dimensions are in the fact-table and the heavier parts of matching and calculations are already there respectively prepared. Ideally your expressions look like sum(Field) or count(Field) and if any conditions are needed they should be made with set analysis.

Of course it won't be always possible but it should be the starting point. A reverse approach by implementing the essential logic within the UI could become very painful. Therefore - are you sure that your data-model is suitable for the UI views?

- Marcus

[QuentinBmt]

Hi Marcus,

Thank you for your quick reply!
I will try to elaborate a little on what I am trying to do.

FYI, we do have a star-scheme data model, but we have a business case that still cause issues.

In our model, we have a company table and a tag table.
A Company can have 0..n Tags attached.

We want to be able to find a company based on a list of tags (Selection).
Here is the complexity of the request: We need to be able to either find all the companies that have at least one of the tags selected (Default behavior of Qlik Sense) OR find all the companies that have at least the selected tags.

To switch between the first behavior and the second one, we have a button on the screen modifying a variable.
To perform the second behavior, we are using the below expression :

Count(distinct {<Soc_Lib_Name={"=Aggr(concat(distinct Tag_Id, ', '), Soc_Lib_Name)=GetFieldSelections(Tag_Id, ', ')"}>} Soc_Id)

This expression is pretty long to load since it is going through all the companies we have in the data model.
This expression corresponds to the following expression when we have less that 2 tags selected :

Count(distinct Soc_Id)

This is why, we are trying to do the following :

if( $(variable) = 1 or GetSelectedCount(Tag_Id) < 2
    , Count(distinct Soc_Id)
    , Count(distinct {<Soc_Lib_Name={"=Aggr(concat(distinct Tag_Id, ', '), Soc_Lib_Name)=GetFieldSelections(Tag_Id, ', ')"}>} Soc_Id)
)

In this case, the result is long to load, since the If() is evaluating both expressions (even if there is no Tags selected).

[marcus_sommer]

It's not surprising that the performance is slow because:

Count(distinct {<Soc_Lib_Name={"=Aggr(concat(distinct Tag_Id, ', '), Soc_Lib_Name)=GetFieldSelections(Tag_Id, ', ')"}>} Soc_Id)

isn't a real set analysis else an if-loop against an aggr() respectively an extra virtual table.

I'm not quite sure if I did understand your scenario right but I could imagine that the following will also do the job:

Count(distinct {< Soc_Lib_Name = p(Tag_Id) >} Soc_Id)

- Marcus

[QuentinBmt]

Indeed, I agree that the expression

Count(distinct {<Soc_Lib_Name={"=Aggr(concat(distinct Tag_Id, ', '), Soc_Lib_Name)=GetFieldSelections(Tag_Id, ', ')"}>} Soc_Id)

is not a real set analysis, but it is the only solution we found to fullfill the requirements.

I am not sure you did get what I am trying to do, I will post an exemple with both expected results .

The model is composed of 3 tables : Company, Tags, Fact.

Expected results with solution 1:

If we select the Tag A

If we select the Tag B

If we select both Tag A & B

Expected results with solution 2:

If we select the Tag A

If we select the Tag B

If we select both Tag A & B (/!\ The difference between the two methods only appear when there are 2 or more tags selected)

The Company B is not selected in this case, because it does not have the Tag A attached to it.

[marcus_sommer]

I'm not sure if I would remain by this data-model. Things which I would seriously consider would be to merge the multiple tags for each company. This might be just a string-aggregation with concat() but there are also numerically approaches thinkable and to it also a mapping-dimension and/or an as-of-table approach, like: The As-Of Table - Qlik Community - 1466130.

Another thought in regard to your last described requirement goes to the use of the AND mode: AND-Mode - Qlik Community - 1471352.

Depending of the number of tags which could be simultaneously selected it may be helpful to categorize them and/or applying several set analysis statements within an expression like:

{ < Tag = {1} > * < Tag = {2}> }

which might be partly outsourced in variables and/or it might be possible to use a multi-layer condition. This means something like:

count({< Tag = p(Tag) >} ID) * -(count({< Tag = p(Tag) >} Tag)=getselectedcount(Tag))

in which the first part counts the ID with an OR connection of the tags and the second parts counts the tags and compared them against the threshold which means both parts acts with an AND to each other (logically it's near to your aggr() method but it should perform better).

So, many ideas ... and it might be also useful to combine them. 

- Marcus

[QuentinBmt]

Thanks Marcus, I will look into that and get back to you if I can find a solution for this.