cancel
Showing results for 
Search instead for 
Did you mean: 

Issue with BEX Formula: else if..

Former Member
0 Kudos

Hi Guru's,

i have issue with formula in Bex where i am writing if else condition.

below is my formula,

here i am actually trying to check the values which are there in C958 if it have value the formula should pick it, if it is empty it should go for next value,i.e. C951 if it have value it should display if it is empty it should go to next so on.

it is working as expected for few cases, and going wrong for few cases.

wrong in the sense it is adding up the values instead of picking the value where it finds.

below are the screens for wrong and correct cases.

please guide us what is going wrong with the formula..

also attached the word pad with screenshots.

Thank you,

Vijay.

Accepted Solutions (1)

Accepted Solutions (1)

KulDeepJain
Active Participant
0 Kudos

Hi Vijay,

Your formula is almost correct but still there will be wrong result for some cases as nesting of bracket is not correct.

Even suggestion will not work properly.

Let us assume :

C958 = A

C951 = B

C949 = C

C948 = D

C947 = E

My understanding is when initial value is non-zero than it should give same value rest should not participate.

NODIM((A<>0)*A) + (A==0)*(((B<>0)*B) + (B==0)*(((C<>0)*C) + (C==0)*(((D<>0)*D) + (D==0)*(((E<>0)*E)))))

This should be perfect.

Former Member
0 Kudos

Hi Jain,

i have tried your formula but some how it is changing in to different after saving the Query,

formula which i changed after your suggestion is below..

But some how it is converting in to below after saving the query and throwing error in the analysis.

please guide me what to do now,

appreciate your idea!

Thank you,

Vijay.

Former Member
0 Kudos

Vijay,

Please try  the formula below with some changes as with  KD Jain's.

NODIM (( A<>0)*A + (A==0) * (((B<>00)*B) + (B==0) *(((C<>0) *C) + (C==0) *(((D<>0) *D) +(D==0)*E)))).

Thanks,

Jai Ganesh J

Former Member
0 Kudos

Ganesh, thank you for your response..

seems like your solution is working for me, let me do some more testing..

Appreciate your help.. thanks a lot..

regards,

Vijay..

Former Member
0 Kudos

Hi Ganesh,

can we handle if we have zeros in this case,

let me explain with screenshot,

as we are writing if A==0 it should go to next condition check, like B<>0*B,

but some time i may have zero percent as a value, this formula will skip that,

do you have any idea to handle this situation..?

in the above screen i am expecting prog percentage as '0' in all the places same like P946 column.

Thank you,

Vijay.

Answers (2)

Answers (2)

Former Member
0 Kudos

If more than 1 conditions are TRUE, it is going to add the value.

To avoid addition it is required to do the proper nesting of conditions.

If you are saying 2nd screenshot is not correct? what is expected?

What's the requirement exactly? Can you illustrate with proper scenarios?

Former Member
0 Kudos

Hi Sucheta,

thank you for your response,

i am expecting the values in the C949 in the chain percentage for the second screen,

you can see in the first screen it has picked the values in C948,

my exact requirement is suppose i have 4 values A B C D,

i need to check the value of A first if it is blank then i should go and check value of B so on till i get the value, where ever i get value it should populate and skip rest of the logic.

Thank you,

Vijay.

Former Member
0 Kudos

Exact formula will be as below;

NODIM(

(A<>0 * A) +

(A==0 * B<>0 * B) +

(A==0 * B==0 * C<>0 * C) +

(A==0 * B==0 * C==0 * D<>0 * D )+

(A==0 * B==0 * C==0 * D==0 * E<>0 * E)

)

Formula given by is also wrong.

If A=3, D = 1

C=0 * D <> 0 will give answer 1 in his case.

Former Member
0 Kudos

Hi Sucheta,

can we handle zero also?

i mean if i have value zero instead of blank, it should also consider the value and populate it,

please see below screen..

according to our formula it is correct, but is it possible if i get zero in any column before any value it should consider zero and keep it in the result,

i understand since we are asking in the formula like A==0 then go to next level, but just wanted to know is there any way we can also catch blank value instead of pointing it as zero,

in the above screen i am expecting my result should be like P946, all zeros..

Thank you,

Vijay.

Former Member
0 Kudos

You need to create one more CKF. Use DELTA function (In formula editor--> Data Functions). It returns Value 1 if Operand is 0 (Real zero not blank) Now use this new CKF in your formula to compare 0 and null.

I can give just hint that you need use DELTA function. I have used it long back and it worked for me.

Former Member
0 Kudos

Go through SAP Note 794006.

Former Member
0 Kudos

Hi

I am just illustrating your columns as alphabets in this example,  try this and see the results.

NODIM ( (A<>0) * A ) + (B<>0 * B) + (C<>0 * C) + (D<> 0 * D) + E)

Former Member
0 Kudos

Hi Jyothi,

Thank you for your response,

i have tried your suggestion its giving *(Stars) in the output, seems like not working.

Thank you,

Vijay.