IF statement help

[former_member1139046]

Hello

I am new to Business Objects. I am trying to construct a statement in Webi but I am having difficulty with the syntax.

In my report I have a tabel with 3 columns, : [Material numberg], [Status], [Version]

I want a statement that will give me the result in new column [Appropriateness] say like: If the [Material number] is the same and it has the [Staus] E and D return "NOT OK" and

If the Material number is the same and it has only the [Staus] D then return "OK"

It also has to had all versions which match apropriate status

Here is the table how i would like to be ia result n the last column

Can anyone assist with how this should be written as a variable?

I got something like this and its not giving me the right answer:

=If ([Material number] = [Material number] Where ([Status] = "E" Or [Status] ="D");"NOT OK";"OK")

Many thanks

[Former Member]

Thank you for visiting SAP Community to get answers to your questions. Since you're asking a question here for the first time, I recommend that you familiarize yourself with: https://community.sap.com/resources/questions-and-answers, as it provides tips for preparing questions that draw responses from our members.

For example, you can:

- outline what steps you took to find answers (and why they weren't helpful)

- share screenshots of what you've seen/done

- make sure you've applied the appropriate tags

- use a more descriptive subject line

The more details you provide, the more likely it is that members will be able to respond. Feel free to also take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html

Should you wish, you can revise your question by selecting Actions, then Edit.

By adding a picture to your profile you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html

[nscheaffer]

It is always helpful if you can provide some usable sample data. I took your screenshot, typed the data into Excel, converted it to SQL, and generated this SQL code which I could then drop into a free-hand SQL query. Now am ready to go with the data you provided.

In order to do this you need to use a calculation context. By default "the calculation takes place in the context of where it appears on the report.

My approach is to create variable called Var Status Count Per Material Number to count the unique status values for a Material number defined as...

=Count([Status]) In ([Material number])

This allows me to take into account the status values of other rows that also have the same Material number.

Now we can create the Var Appropriateness variable...

=If([Var Status Count Per Material Number] > 1; "Not OK"; "OK")

You can remove Var Status Count Per Material Number. I only have it in the table to show what is happening. You can also do some conditional formatting if you want to turn Var Appropriateness red or green depending on its value.

Note that you will also get "OK" if all the statuses for a give Material number are the same even if they are not "D". You did not seem to be looking for that so I did not provide a solution for that. If you do need that functionality, the variables will need adjustments or perhaps a different method entirely will need to be pursued.

Noel