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

[Former Member]

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

[Former Member · Accepted solution]

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.

[anshu_lilhori · Accepted solution]

Hi,

If else condition at bEx level works only on numbers it cant read string values so if you want to read value AA then it seems to be not possible.

In case you need to change the same then you can do it at transformation level with the help of simple routine.

Hope that helps.

Regards,

AL

[Former Member · Accepted solution]

Hi Anand,

can't we achieve this by creating the structure in the query and include the charecterstics init then create the formula based on that ?

Thanks,

Braj

[vinay_lohakare5]

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

[Former Member]

Hi All,

Thanks for All,

you are suggestions are more valuable but not sufficient to fix the current issue, fixing this issue in the transformation level or removing the corrupted records or reloading the infocube will take care of this issue but my current sap BI design is supporting for this I tried all possible ways but nothing worked for me (as mentioned earlier this is an historical fix, incorrect records have flown long before). So now the only option is to fix this issue in the Bex query level. I need your inputs to achieve this…

Eagerly waiting for your inputs..

Thanks,

Braj

[anshu_lilhori]

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

[lou_matura1]

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

[Former Member]

Hi Lou,

your suggestions are more valuable but not sufficient to fix the current issue, fixing this issue in the transformation level or removing the corrupted records or reloading the infocube will take care of this issue but my current sap BI design is supporting for this I tried all possible ways but nothing worked for me (as mentioned earlier this is an historical fix, incorrect records have flown long before). So now the only option is to fix this issue in the Bex query level. I need your inputs to achieve this…

Eagerly waiting for your inputs..

Thanks,

Braj

[lou_matura1]

Braj.

Can you email me screenshots in an XLSX file format of what you have done/attempted thus far?  I would like to see what you have in your rows and in the calculation that you are doing/have set up?  If you can send me a sample of the output at the "key" level, that would be great...  I don't want to see the text fields (a possible confidentiality issue).

Thanks

Lou

[Former Member]

Hi Lou,

Thanks for your response..

could you please help me in other way by writing the IF THEN ELSE formula syntax in bex query for the above requirement?

Thanks,

Braj

[lou_matura1]

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