Hi all,
I have a following problem. In my company we have dynamic changing targets. For Example this month the target is on Products from Cat1 (this is under CATEGORY_1 column) and Br1 (this is under BRAND column) and PT1 (this is under PackType column) and the next month the target would be for products from SubCat2 (this is under SUBCATEGORY column) and PS2 (this is under PackSize column).
CATEGORY_1, BRAND, PackType, SUBCATEGORY, PackSize are column in my Dim_Products Query.
I want to add a conditional column in my Dim_Products Table to use it as filter in my report, where it will bring 1 when the target conditions are met and 0 otherwise.
I have a Target_Mapping Table where I have the columns Names from my Dim_Products table and the value.
| Name | Column_Name_1 | Value_1 | Column_Name_2 | Value_2 | Column_Name_3 | Value_3 |
| Target_September | CATEGORY_1 | Cat_1 | BRAND | Br1 | PackType | PT1 |
How can I write the if statement in my Dim_Products table
Instead of hard-coding the [CATEGORY_1], [BRAND], [PackType] the columns reference to change it with the refence of the Target_Mapping Table under columns with header starting with "Column_Name_", picking each time the first row and put it as column reference in the if statement and as value again picking whatever is in the first row of columns with headers starting with "Value_".
if [Column_Name_1 first row] = Value_1 first row and [Column_Name_2 first row] = Value_2 first row and [Column_Name_3 first row] = Value_3 first row then 1 else 0
Thank you in advance,
Eleftheria
