Create Categories based on multiple Dimension Values and Conditions

[AjjuSid]

Hello Experts, 

I need help with the problem below for creating a  Calc dimension using the If Statement, I have a table with column names T_Name and Category(A, B, C). I have to create a new dimension based on the Category Values.  For this, I was able to use the if Condition in the script layer to create a Calc dimension by using
                            iF(isnull(Category), 'Non-Category'
                                  if(Category ='A', 'Cat A',
                                         if(Category ='B', 'Cat B',
                                                  if(Category =' C', 'Cat C'
                                          )))) as Flag_Cat

I  use FLag_cat in the Bar chart to show the count of each category or use it against different measures, Due to recent changes in the process, we noticed that Each dim Value could be part of one or multiple categories i.e. A and B or B and C, or C and A or A and B and C. How do I modify the if statement to show new categories?  I tried with the below expression but it's now working as expected as it is still giving me only 4 categories (non, Cat A, Cat B and Cat C). I want it show Cat AB, Cat BC and Cat CA along with the other 4. 

                             iF(isnull(Category), 'Non-Category'
                                  if(Category ='A', 'Cat A',
                                         if(Category ='B', 'Cat B',
                                                  if(Category ='C', 'Cat C',
                                                          if(Category ='A'  and Category ='B', 'Cat AB',
                                                                 if(Category ='B' and Category ='C' , 'Cat BC',
                                                                    if(Category ='C' and Category ='A', 'Cat CA', 
                                                                     if(Category ='C' and Category ='A' and Category = 'B', 'CAT ABC' 

                                                                                  ))))))) as Flag_Cat 

Please let me know if any changes are required or need a new approach. Thanks in advance for the help

@tresesco @krishna_2644 @Chanty4u 

[rubenmarin]

Hi, 2 comments:

- The if conditions returns the first 'true' condition, so the most restrictives should go first. So, set the condition for ABC as the first 'if', then AB, BC and CA, and lastly the others.

- Each row will only have one value for a category, so nonen of them will return true for any of the and's.

You can try with an auxiliar table using a group by [dimensionField] and a mix of concat() and substring() or index(), this table will be linked to the main table by the dimension field.

Categories:
LOAD
  [DimensionField],
  If(hasA and HasB and hasC,'CAT ABC',
    If(hasA and hasB,'CAT AB',
      If(hasA and hasC,'CAT AC',
        If(hasB and hasC,'CAT BC',
          If(hasA,'CAT A',
            If(hasB,'CAT B',
              If(hasC,'Cat C','Non-Category'))))))) as Flag_Cat
;
LOAD 
  [DimensionField],
  If(Index(Concat(Category),'A'),1,0) as hasA,
  If(Index(Concat(Category),'B'),1,0) as hasB,
  If(Index(Concat(Category),'C'),1,0) as hasC
Resident
  DataTable
Group By
  [DimensionField]
;

[TauseefKhan]

Hi @AjjuSid,

check this:

if(isnull(Category), 'Non-Category',
if(wildmatch(Category, '*A*') and wildmatch(Category, '*B*') and wildmatch(Category, '*C*'), 'Cat ABC',
if(wildmatch(Category, '*A*') and wildmatch(Category, '*B*'), 'Cat AB',
if(wildmatch(Category, '*B*') and wildmatch(Category, '*C*'), 'Cat BC',
if(wildmatch(Category, '*C*') and wildmatch(Category, '*A*'), 'Cat CA',
if(wildmatch(Category, '*A*'), 'Cat A',
if(wildmatch(Category, '*B*'), 'Cat B',
if(wildmatch(Category, '*C*'), 'Cat C'
)
)
)
)
)
)
)
) as Flag_Cat

[AjjuSid]

Unfortunately, This is giving the same result as mine