Using If Else on multiple fields

[Former Member]

Is there a way to create an object in a universe that is based off four other fields? For example, I want to have a field that can have four different values -- A, B, C, D. It will be a string format. The value that populates is based off upto four other existing string fields. Using basic If Else statement, it would look like this:

If

Field1<>"Z" Then "A"

Else

If Field1="Z" AND Field2<>"Z" Then "B"

Else

If Field1="Z" AND Field2="Z" AND Field3<>"Z" Then "C"

Else

If Field1="Z" AND Field2="Z" AND Field3="Z" AND Field4<>"Z" Then "D"

Is this possible within Designer? It's built off SAP BW. If not, what would be the best way to go about it.Thanks.

[amitrathi239]

Hi,

One way you can do it on the webi level by If Else condition.

Thanks,

Amit

[Former Member]

Right, I can create a variable in WebI but I can't use variables in the selection screen. I want the users to able to run the report off the new field.

[Former Member]

Use a case statement in your object definition. The basic logic would be:

CASE WHEN Field1 ='Z' Then 'A' 
WHEN Field1='Z' AND Field2='Z' Then 'B'
WHEN Field1='Z' AND Field2='Z' AND Field3='Z' Then 'C'
WHEN Field1='Z' AND Field2='Z' AND Field3='Z' AND Field4'Z' Then 'D'
END

However, the last three options would never happen because case statements finish upon satisfying the earliest condition. In your example, whenever Field1='Z', you will always output 'A'. Also, you have no ELSE variable at the end for when Field1 is not Z

So, something like this would be better:

CASE 
WHEN Field1='Z' AND Field2='Z' AND Field3='Z' AND Field4'Z' Then 'D'
WHEN Field1='Z' AND Field2='Z' AND Field3='Z' Then 'C'
WHEN Field1='Z' AND Field2='Z' Then 'B'
WHEN Field1 ='Z' Then 'A' 
ELSE 'E'
END

If you need any further clarification, let me know.

Regards,

Mark

Edited by: Me map on Jun 16, 2011 9:56 AM

[Former Member]

I tried putting that in the Select clause and it didnt work. But when I put that in the Where clause it worked. If that's the case, what do I put in the Select clause?

[Former Member]

What have you put in the select clause and how didn't it work?