Nested If Condition

[siva0606]

Hi All,

Code	Condition 1	Condition 2	Condition 3
A001	Type A
B001		Type B	Type B expect I001
C001		Type B	Type B expect I001
D001		Type B	Type B expect I001
E001		Type B	Type B expect I001
F001		Type B	Type B expect I001
G001		Type B	Type B expect I001
H001		Type B	Type B expect I001
I001		Type B
J001	Type A

I have written the following condition to achieve the above results. Since 2nd condition is being satisified, it is not displaying the third condition as expected.

=If(Match(Code,'A001','J001'),'Type A',

If(Not Match(Code,'A001','J001'),'Type B',

If(Not Match(Code,'A001','J001','I001'),'Type B Expect I001')))

I tried using Pick Match and Valuelist too, but could not bring the results as expected.

Kindly help me to get the actual results.

[Vegar]

Try changing the order of your nested IF. Like this:

=If(Match(Code,'A001','J001'),
    'Type A',
    If( Not Match(Code,'A001','J001','I001'),
        'Type B Expect I001',
        If( Not Match(Code,'A001','J001'),
            'Type B'
        )
    )
)

[Vegar]

With the given sample data set you could also use another nested if approach.  Try this:

=if(len([Condition 3]), [Condition 3],
    if(len([Condition 2]), [Condition 2],
       if(len([Condition 1]), [Condition 1])))

[MayilVahanan]

HI

I think, you are trying to get 3 fields?

If so, try like below

If(Match(Code,'A001','J001'),'Type A') as Condition1,

If(Not Match(Code,'A001','J001'),'Type B') as Condition2,

If(Not Match(Code,'A001','J001','I001'),'Type B Expect I001') as Condition3

Reason for 3 condition not satisfied in nested if because, mostly condition2 & condition3 are looks same, so it gives condition 2 result.

[siva0606]

Hi Mayilvahanan,

Sorry not preferring as 3 separate fields , 'Type A' , 'Type B' , 'Type B Except I001' should be aliased as Type

All three Types in a single column as Type.

Best Regards,

Siva

[siva0606]

Hi Vegar,

I tried changing the order, but it was not working.

Best Regards,

Siva

[Vegar]

If you want to do it in the script, then try this.

LOAD Code,
   if(len([Condition 3]), [Condition 3],
      if(len([Condition 2]), [Condition 2],
         if(len([Condition 1]), [Condition 1]))) as Type
Inline [
Code, Condition 1, Condition 2, Condition 3
A001, Type A,      ,            
B001, ,            Type B,      Type B expect I001
C001, ,            Type B,      Type B expect I001
D001, ,            Type B,      Type B expect I001
E001, ,            Type B,      Type B expect I001
F001, ,            Type B,      Type B expect I001
G001, ,            Type B,      Type B expect I001
H001, ,            Type B,      Type B expect I001
I001, ,            Type B,
J001, Type A,      ,
];

[siva0606]

Hi , 

By any chance to do this in front end , why because there are many so many joins and concatenations involved in the backend . so thinking it may affect the existing numbers. Kindly let me know

[siva0606]

Also tried this is in backend, but not providing the expected results , when we select Type B it should show from B001 to I001 , but it actually shows only I001, which is not correct.

[Vegar · Accepted solution]

Is it possible for a Code  to be associated with more than one type? 

Then you could just add the Condition as a new dimension table in your data model. Consider this data model with a two column transaction table and the new Type dimension table.

It will give you an association for each Code to all it Types like this: