Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
user467341
Creator II
Creator II

Optimise IF AND statement?

Hi Qlik experts,

I have this expression in my table dimension column:

=Aggr( CONCAT( DISTINCT 
	IF(ISNULL(TableC.[Signed_In], 
    	AND TableA.[NewPlayer] = '1'
        	AND TableA.GameID = TableB.GameID)
            	, TableF.PlayerNo & ' (' & TableF.PlayerLvl & ') ' ), ','),  TableC.MainGameID)

The chart table has around 3500 data rows, with about 15 columns (among them around 6 calculated on the fly expression, including this).

After adding this expression, I noticed the table calculation loading time increased significantly. I think it is due to the IF AND statement.

Is there a way to optimise this? Is set analysis an alternative? I am not familiar with high level set analysis syntax, but I can say that TableA.[NewPlayer] = '1' and TableA.GameID=TableB.GameID doesn't need to be inside the IF statement.

Appreciatei someone can advice and suggest a way to optimise this without touching the data model. Thank you.

Labels (3)
3 Replies
BrunPierre
Partner - Master II
Partner - Master II

Let's say, Count of TableB.GameID is the measure, then try this for the set analysis.

Count({$<
TableC.[Signed_In]={"=Len(TableC.[Signed_In])=0"},
TableA.[NewPlayer]={1},
RecordID={"=TableA.GameID=TableB.GameID"}//RecordID is unique for each row in the table
>} DISTINCT TableB.GameID)
user467341
Creator II
Creator II
Author

Hi Brun,

In this case the expression is not for count, but to filter string data and concat them into a cell.

If it's not measure, how should I approach?

Thank you.

 

marcus_sommer

In general are if-loops and aggr() constructs the opposite of a performance-optimization and should be therefore avoided and be replaced with an appropriate logic within the data-model.

This means mainly to develop the data-model as star-scheme with a single fact-table and n dimension-tables. All relevant fields would reside with one table and no query of tabA.Field = tabB.Field is needed anymore and if these fields couldn't be directly merged the check would be applied within a flag-field. Similar with any NULL - if such query is really relevant replacing the NULL with a real value like '<NULL>' is better or also creating a flag-field for it.