I have the below Nested IF statement as a DIMENSION expression in my straight table but now my QVW is VERY SLOW when on open. I am using this dimension expression to hide rows or values when the value is null because the rules don't apply. It seems I can not use the below IF statement in the script because all the fields in the conditions belong to different tables unless I combine all the tables into one table. It doesn't make sense to do this. I can't use set analysis because it is not a calculated expression, right? I also tried the PICK/MATCH functions but I can't seem to get the syntax working. Any help/suggestions are greatly appreciated.
=if([VARIANCE ID] = 14 AND [Management Line]='Structured Finance' AND [Business Group 1] <> 'Covered Bonds', Null(),
if([VARIANCE ID] = 15 AND [Management Line]<>'Structured Finance' AND [Business Group 1] <> 'Covered Bonds', Null(), 1))
All very valid points! A couple of things to note...the ISSUERRATINGFACT is technically not a fact table. Our DBAs just named it that. Again, I am not doing any metric calculations in my QVW so there are NO measures/calculations.
Rahul to your point. I can't take my expression to the script UNLESS I JOIN all my tables or most of my tables into 1(or maybe 2) in-memory tables. Right now, the expression is based on many fields in different tables in my data model.
Marcus, I can't use set analysis because I am not aggregating or "measuring" any values. All columns in my tables are dimension values. The sole purpose of the QVW is to display details only. The expression that I posted in my initial post of this discussion thread was an example of just 1 calculated dimension that I used in the QVW. There are 2 more nested IF calculated dimensions that I use in my table too. I just gave you one as a simple example. I can't use APPLYMAP because my tables have many fields I need to bring in - they are not "lookup" values.
I am still analyzing my data model and trying to see if I can use many of the approaches mentioned above.
I appreciate everyone's input. Lots of great info!
I did use that set analysis expression and it works! However, I have another calculated dimension in the same table that is causing performance issues. See below NESTED IF calculated dimension...
=if(Match([%EMPLOYEE_NO],[SECONDARY_EMP_NO]) AND Match([%EMPLOYEE_NO],[SURV_EMP_NO]),'Secondary & Surveillance',
The fields %EMPLOYEE_NO are in the COMMITTEE table and the fields SECONDARY_EMP_NO and PRIMARY_EMP_NO and SURV_EMP_NO are in the ISSUERATINGFACT table.
I think I am going to try to combine (JOIN) the COMMITTEE table and ISSUERATINGFACT table in the script so I can use the above expression in the script.
