Skip to Content
author's profile photo Former Member
Former Member

How to create If- then- else statement in Bex query

Hi All,

I have a requirement to replace the value of the Characteristic info object with another value of the same Characteristic info object in the Bex query, To do this I have created structure that contain Characteristic info object, then created the formula by using Boolean functions in the bex but I did not give the desired result. Please go through the example mentioned below and suggest me how to achieve this.

Example: There is a Characteristic info object called 0comapny_code which contains the values like 11, 22,AA, 33,44,55,…so on.

0comapny_code

11

22

AA

33

44

55

In the above example by mistake user entered ‘AA’ instead of ‘11’ in the source system, so then same information has flown from source system to infocube (tried to fix this in cube level but did not work bcz this is an historical fix). so now I want to replace ‘AA’ with ‘11’ in the bex query level. Please suggest me to achieve this.

Thanks in Advance

Braj

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 26, 2012 at 11:29 AM

    Hi,

    If -then - else condition applicable for key figure.

    sysntax : ( KF boolean operatior value ) * result + (KF2 boolean operator value) * result

    display result for true condition , & move to next condition for false condition

    I will suggest to correct data at infoobject level . it is not advisable to display incorrect data.

    Regards,

    Anand Mehrotra.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 26, 2012 at 12:45 PM

    Braj.

    I would suggest that you fix the source data in this case. If AA is not supposed to be in there, and it's invalid you're asking for problems down the road if you don't.

    I realize that there is a cost to redoing the work, but garbage in, garbage out. I know that this isn't the answer that you're looking for BUT the goal is to have clean data.

    Lou

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Braj.

      Sorry for the delay... I had to take off for a bit. In case you haven't gotten this figured out, I wouldn't look at an IF-Then-Else statement unless you are using it in a formula.

      I'm assuming that you are having the 0Company as a row. What I would do is create a structure on the row for all of 0Company and combine AA and 11 into the same row. You will have to create something for each of the 0Company codes.

      Also, and this is important from a balancing perspective, create a selection that excludes all of the 0Company codes that you are puttting into the current structure in case new ones get added. This way you will see that you have to make adjustments to your structure. The bottom line totals will match back to your other reports.

      I hope this helps.

      Lou

  • Posted on Oct 26, 2012 at 02:21 PM

    Hello Braj,

    In your case the data itself is wrong from the source.

    Please correct the data record in source and load it again in BI. So you will have the correct record.

    In BEx query you can restrict <> AA or you can delete the record using selective deletion also.

    Thanks,

    Vinay

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi,

      At Query level i do not see any scope to correct this.I can suggest you a workaround at modeling level to do the same.

      Steps to follow:

      1. Make a copy of the existing cube.
      2. Create transformation between original cube to copy cube.
      3. Load the data with a filter of company code = AA
      4. Now again create transformation between copy cube to original cube and this time write the simple field level routine to replace AA with 11.
      5. Load the data back from copy cube to original cube.
      6. Once you confirm the data integrity then you can do selective deletion on comp code = AA.

      In the above mentioned way you will be able to replace the comp code data AA with 11.

      Discuss the same with your leads and solution architects about this method if you get a sign off then go ahead with it.

      Regards,

      AL

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.