Optimise IF AND statement?

[user467341]

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.

[BrunPierre]

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]

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.