Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
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'
)
)
)
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])))
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.
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
Hi Vegar,
I tried changing the order, but it was not working.
Best Regards,
Siva
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, ,
];
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
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.
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: