Query Requirement

[Former Member]

I have a requirement in Query that is:

For a particular Sales Order No and Sales Order Item and InfoObject "Y" =1,

if         InfOObject   "X" = AB,  add        KF1,
else if InfoObject    "Z" = AC, subtract KF1

Note: InfoObject Y, X & Z are characteristics not KeyFigures

How can this be achieved in Query Designer?

Thanks in advance.

Regards,
Pramod

[Former Member]

There was a small mistake in what is mentioned above

For a particular Sales Order No and Sales Order Item and InfoObject "Y" =1,

if         InfOObject         "X" = AB,  add        KF1 (for that particular SO and SO Item),
else if InfoObject   "Z" "X" = AC, subtract KF1 (for that particular SO and SO Item)

Note: InfoObject Y,& X  Z are characteristics not KeyFigures

Hope this example helps.

[Loed]

Hi,

Can you give other samples?

What do you mean by add or subtract KF1? Add all the KF1 and subtract all the KF1 when AB and AC, respectively?

I mean,there is no problem in the addition,but in the subtraction part how will you know which number is the MINUEND in your example? For example the 4th line has a value of KF1 = 20 so the result will be -10?

MINUEND - SUBTRAHEND = DIFFERENCE

Regards,

Loed

[Former Member]

Hi Loed,

I do not have other example, sorry for that.

Yes it means we need to add KF1 when AB and subtract when AC.

It might give negative results sometimes which I think is a problem.That should be OK.

But to how to achieve this is my quesiton. When IO's X and Y are Characteristics (so can't be used in a Formula). Also how to define the condition that when it is equal to AB, do addition and when equal to AC apply subtraction.

Is it achievable? If yes, please let me know the approach.

Regards,

Pramod

[Loed]

Hi,

How about creating additional three (3) columns for your keyfigures?

Say,

KF1_AB

X - filter with AB

KF1

KF1_AC

X - filter with AC

KF1

SUM_AB_AC

Combine the two (2) keyfigures to have an output of single column..

Is this really the report layout? Please provide the FINAL layout of the report..

Regards,

Loed

[Former Member]

HI Loed,

Thanks for your solution, will try it in Query.

The report layout will be something similar to the one shown below, KF2 is the result of addition and subtracion and they will be in the single column. Ofcourse there are many other fields which

are not shown

 

I have to create two RKF's

RKF_Y_AB

  KF1

      restrict by SO

             restrict with SO Item

                       restrict with Y - 1

                                  restrict by X - AB

RKF_Y_AC

   KF1

      restrict by SO

             restrict with SO Item

                         restrict with Y - 1

                                  restrict by X - AC

So how to do this addition part when InfoObject X = AB & subtraction when X = AC.

The way you have explained I believe will not give solution to the subtraction part at least.

Correct me if I have missed something in understanding your solution.

Regards,

Pramod

[Loed]

Hi,

What I can't understand is how are you going to choose which is the MINUEND or the SUBTRAHEND..

Do you always have two (2) entries as shown in your example? If not, what if you have more than 2 values, let's say, the values are 10, 10, 50, and 40 for objects which has a value of X = AC? What will be the result?

Regards,

Loed

[Former Member]

Hi,

In that case the result will be equal to KF = -110. That should be fine I believe.

In the scenarios where we are dealing with Debit or Credit values, negative values make sense right?

Regards,

Pramod

[Loed]

Hi,

So result will be -100?

10 - 10 - 50 - 40 = - 100

Is that right?

What if 50 is the first in row?

Result will be different..

50 - 10 - 10 - 40 = -10

May I know the logic you wanted?

Regards,

Loed

[Former Member]

Hi Loed,

The point made by you is absolutely right. Depending on the what is in first place the value varies.

But let's forget that as I do not have complete details of what is required.

Without giving much consideration to the values or the result, I wanted to know if it is doable

Adding and subtracting both depending on Characteristic values.

Regards,

Pramod

[former_member199945]

Hi ,

Instead of doing at query level , better try do same at transformation level . At transformation level it is easy to do it.

I have to create two RKF's

RKF_Y_AB

  KF1

      restrict by SO

             restrict with SO Item

                       restrict with Y - 1

                                  restrict by X - AB

RKF_Y_AC

   KF1

      restrict by SO

             restrict with SO Item

                         restrict with Y - 1

                                  restrict by X - AC

after creating RKF create CKF/formula  use count(RKF_Y_AB) it will return 1 if record type with AB exists else it will show 0.

Thanks.

[Former Member]

Hi Seshu,

How does it help creating a CKF with COUNT function with the above RKF's.

Can you please explain in detail.

I do not want to do it in the Transformation to achieve this so trying it in BEx.

Thanks in advance,

Regards,
Pramod

[Loed]

Hi,

Try this:

KF1_AB

X - filter with AB

KF1

KF1_AC

X - filter with AC

KF1

KF1_DUMMY

X - filter with AC

KF1

exception aggregation FIRST VALUE

(try first without choosing a reference characteristic, if it didn't work then try using with reference characteristic based on X object)

SUM_AB_AC_DUMMY

KF1_AB + KF1_DUMMY - KF1_AC

If that works, the logic for the difference will be, let's say the first value is 10, so we need to get -100:

KF1_AB + KF1_DUMMY - KF1_AC

0 + 10 - 110 = -100

I'm not sure though if it will work..

Anyway just try it..

Regards,

Loed