Optimizing Qliksense Pick and Match Expression for Pivot Table which ahs drill down

[Sachin96]

Hi,

I need an help on optimizing the Pick and match conditional expression which is taking time to load the pivot table once the drill down is selected.

here is the code for it

pick(Match(Dimensionality(),4,8,9),
if(Match([Level6 Monthly Operator],'0.00') and Match([Level6 Monthly Ultimate limit],'NA'),'No',
if(Match([Level6 Monthly Operator],'0.00','AND','OR') and not Match([Level6 Monthly Ultimate limit],'NA') and Column(vVarcol)>[Level6 Monthly Ultimate limit],'Yes',
if(Match([Level6 Monthly Operator],'AND') and Column(vVarcol)>[Level6 Monthly Amount limit] and Round(Column(vVarpercentage)*100,2)>[Level6 Monthly Percentage limit],'Yes',
if(Match([Level6 Monthly Operator],'OR') and (Column(vVarcol)>[Level6 Monthly Amount limit] or Round(Column(vVarpercentage)*100,2)>[Level6 Monthly Percentage limit]),'Yes','No')))),
if(Match([Level10 Monthly Operator],'0.00') and Match([Level10 Monthly Ultimate limit],'NA'),'No',
if(Match([Level10 Monthly Operator],'0.00','AND','OR') and not Match([Level10 Monthly Ultimate limit],'NA') and Column(vVarcol)>[Level10 Monthly Ultimate limit],'Yes',
if(Match([Level10 Monthly Operator],'AND') and Column(vVarcol)>[Level10 Monthly Amount limit] and Round(Column(vVarpercentage)*100,2)>[Level10 Monthly Percentage limit],'Yes',
if(Match([Level10 Monthly Operator],'OR') and (Column(vVarcol)>[Level10 Monthly Amount limit] or Round(Column(vVarpercentage)*100,2)>[Level10 Monthly Percentage limit]),'Yes','No')))),
if(Match([GL Account Monthly Operator],'0.00') and Match([GL Account Monthly Ultimate limit],'NA'),'No',
if(Match([GL Account Monthly Operator],'0.00','AND','OR') and not Match([GL Account Monthly Ultimate limit],'NA') and Column(vVarcol)>[GL Account Monthly Ultimate limit],'Yes',
if(Match([GL Account Monthly Operator],'AND') and Column(vVarcol)>[GL Account Monthly Amount limit]and Round(Column(vVarpercentage)*100,2)>[GL Account Monthly Percentage limit],'Yes',
if(Match([GL Account Monthly Operator],'OR') and (Column(vVarcol)>[GL Account Monthly Amount limit] or Round(Column(vVarpercentage)*100,2)>[GL Account Monthly Percentage limit]),'Yes','No')))))

its a calculated condition by using the dimensionality for particular drilldown i need to display Yes or No value. as soon as i select the level in dimension my pivot table visualization takes time to display . i need to optimize this so that it wont take time to display the table when selected the value in drilldown.

got an input from someone on validating the else condition first and then incrementing the if condition with applying +1 for rest . i did not get how exactly it is. can someone help me in this 

[Sachin96 · Accepted solution]

Okie, so i have got partial solution for this. Actually issue was with Dimension which had Hiphens in different drill down level. So when i selected that drilldown which had Hiphens, it used to take time to display the values, i have deleted those columns now , anyhow the values for those columns wer hiphens aswell,

so my question now is ther any way other then deleting the column we can remove the hiphen values. suppress null or do not show null values are not working. and i have asked the team to give data for those columns so that we can check if the table still takes time to load with actual data