Can I Use IIF formula in this case

I'm trying to find a solution for this issues

ACCOUNT CUSTOMER PRODUCT DATA TYPE VALUE

VXXXXXX 7566 CSI QTY 300

VXXXXXX 9999 CSI QTY 400

VXXXXXX 9999 CON QTY 100000

VZZZZZZ 7566 CSI % 0,05

VZZZZZZ 9999 CSI % 0,10

The % is linked with the Quantity and Product

I need to copy the 300 and 400 in an other account but I don't want the 100000 because I have no % for the PRODUCT CON. IF product is same in compte VZ... and VX... I nned to copy the 300 and 400 in another account not the 100000 same logic I don't have code CON in account VZ for the Customer

Can I use a IIF statement ? And if yes how can I code this in a script ?

SAP Managed Tags:
SAP Business Planning and Consolidation, version for SAP NetWeaver

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (2)

I'd think you can achieve same even without using IIF.

Try scoping DATA TYPE =% or ACCOUNT = VZZZZZZ or both depending on your requirements.

Then in REC use EXPRESSION = [DATA TYPE].[QTY], [ACCOUNT].[VXXXXXX] and other Dimensions that are different between % an QTY rows.

Idea is that it will choose only those rows that have value with % and take amounts from corresponding rows with QTY.

@Vadim,

Am I missing something here?

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Gersh,

Looks good It will simplify the script! But the original script is also correct...

Vadim

P.S. The only difference will be in case of 0 stored in PERCENT - with my code the target will not be updated, with your - will be!

P.S. And zero's can happen in case of clear and data reload...

Hi Frederic,

The code with ternary operator will do the job:

*XDIM_MEMBERSET ACCOUNT=VXXXXXX

*XDIM_MEMBERSET DATATYPE=QTY

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=([ACCOUNT].[VZZZZZZ],[DATATYPE].[%])==0 ? ([ACCOUNT].[ACCTARGET],[DATATYPE].[DTTARGET]) : %VALUE%, ACCOUNT=ACCTARGET,DATATYPE=DTTARGET)

*ENDWHEN

Where ACCTARGET - target ACCOUNT, DTTARGET target DATATYPE

Vadim

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Sorry Frederic!

Please provide the REAL names of ACCOUNT, DATATYPE and other dimension members.

What source you want to copy?

What condition you want to test?

Where you want to copy the result?

In general, ternary operator works like:

A==B ? C : D

If A=B then write to destination C, else D

Tuple is the intersection definition like:

([DIM1].[MEM1],[DIM2].[MEM2],[DIM3].[MEM3])

surrounding brackets for tuple - required!

With ternary operator if for some condition you want to write nothing - then write destination over destination.

Hope it's clear!

Vadim

HI Vadim

I try few different way to code my requirement...may be it will be a BADI required

My requirement if copy only value in ACCOUNT VX ( in my example ) ... if I have value in ACCOUNT VZ.... Based on product, In my exemple we have Product CSI and CON but not in ACCOUNT VZ. We Have only Product CSI ( I should be have different product code is not static. I need to check if similar code is in both account if YES copy value if not do nothing

SOURCE : ACCOUNT IN ACCOUNT VZ

CONDITION : Explained in my statement

I want to Copy on another account ( in my code I put VCONC_W but it will be VCONC_W_SUM_CALC

In Yellow is an exemple of code but see my requirement

Sorry, you want me to retype code from your picture????

"SOURCE : ACCOUNT IN ACCOUNT VZ

CONDITION : Explained in my statement

I want to Copy on another account ( in my code I put VCONC_W but it will be VCONC_W_SUM_CALC"

Is it so hard to list ALL dimensions and members?

Vadim

LOL sorry for all the trouble. I was not sure of my code I put it as reference with the print
screen. Sorry again

I hope I put all what you need :

*XDIM_MEMBERSET DAILY TIME = BAS(%DAILYTIME_SET%)

*XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET ACCOUNT = VCONC_W

*WHEN DATATYPE

*IS VOLUME

*WHEN CUSTOMER

* IS 40007566,40007606 // REPLACE BY * AFTER TESTING

*REC(EXPRESSION = ([ACCOUNT].[VCONC_H20],[DATATYPE].[PERCENT]) == 0 ?

// I don't know how to code properly the requirement for the true or false A : B

//Next line is the target copy

ACCOUNT = VCONC_W_SUM_CALC, DATATYPE = VOLUME, DATASOURCE = AUTOCALC, CUSTOMER = NO CUSTOMER, PRODUCT = NOPRODUCT, DAILY TIME = %VERSION_ENDMTH)

*ENDWHEN

*COMMIT

as per my example : need to copy 1000 and 500 because product code CON VCONC_W and VCONC_H2O but the PRODUCT CODE CSI not need to copy it because it's not assigned to account VCONC_H20

ACCOUNT CUSTOMER PRODUCT DATATYPE VALUE

VCONC_W 40007566 CON VOLUME 1000

VCONC_W 40007566 CON VOLUME 500

VCONC_W 40007606 CSI VOLUME 2000

VCONC_H20 40007566 CON PERCENT 0,43

VCONC_H2O 40007566 CON PERCENT 0,34

I need to retrieved the volume IF SAME PRODUCT code is listed / Or exclude th one not in the ACCOUNT = VCONC_H20 here CSI

( in my case I need to copy [ACCOUNT].[VCONC_W] with value 1000 and 500 ( total 1500) because PRODUCT code CON is under the ACCOUNT = VCONC_H20. Exclude the 2000 assigned to VCONC_W because the code CSI is not in the ACCOUNT= VCONC_H20 ( here is an exemple I can have multiple PRODUCT code ) If the PRODUCT CODE is in VCONC_W and VCONC_H20 I need to copy it into [ACCOUNT].[VCONC_W_SUM_CALC],[DATATYPE].[VOLUME],[DATASOURCE].[AUTOCALC],[CUSTOMER].[NOCUSTOMER],[PRODUCT].[NOPRODUCT]

Dimension & Member ID :

VERSION = ACTUAL

ACCOUNT = with ID VCONC_W, VCONC_H20, VCONC_W_SUM_CALC

DATA TYPE = VOLUME, PERCENT

CUSTOMER = 40007566, 40007606 , NOCUSTOMER for the target

DATA_SOURCE = AUTOCALC for the target

PRODUCT = NOPRODUCT for the target,

Trying to answer myself (I was asking the following info):

Source scope (to be defined with XDIM_MEMBERSET):

DATATYPE=VOLUME

ACCOUNT=VCONC_W

PRODUCT=<ALL>

VERSION=ACTUAL

DATASOURCE=SORCEDATASRC - you don't provide info!!!!

CUSTOMER=<ALL>

TIME=%DAILYTIME_SET%

Test (logical condition in ternary):

DATATYPE=PERCENT

ACCOUNT=VCONC_H20

PRODUCT=<ALL>

VERSION=ACTUAL -?

DATASOURCE=SORCEDATASRC -?

CUSTOMER=<ALL>

TIME=%DAILYTIME_SET% -?

Target (target changes in bold):

DATATYPE=VOLUME

ACCOUNT=VCONC_W_SUM_CALC

PRODUCT=NOPRODUCT

VERSION=ACTUAL

DATASOURCE=AUTOCALC

CUSTOMER=NOCUSTOMER

TIME=%DAILYTIME_SET%

Is it correct?

Vadim

Hi Vadim

Yes what you put in scope is what I what to scope

*SELECT - TO DEFINE VARIABLE %DAILYTIME_SET% // set at the last day of the month for posting the value retrieved

*XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET CUSTOMER = < ALL >

*XDIM_MEMBERSET PRODUCT = < ALL >

*XDIM_MEMBERSET DATATYPE = VOLUME

*XDIM_MEMBERSET DATASOURCE = PIFCO

*XDIM_MEMBERSET DAILYTIME SET = BAS(%DAILYTIME_SET%) // Variable find in *SELECT

For the section test logical

In bold Is my logical. We need to check if the Percent is there for ACCOUNT = VCONC_H2O. IF YES , I need to copy the value of account VCONC_W to the TARGET. I Need to do that if the PRODUCT CODE is there too in both account ( in My exemple CSI is only on VCONC_W I dont' need to copy the value assigned to CSI. This code is not assigned in ACCOUNT VCONC_H2O BUT ONLY IN VCONC_W ).

For the TIME = %DAILYTIME_SET%. I set this variable with a property in DAILYTIME dimension. with The DAILYTIME dimension I have posting each days so I retrieved all the volume for the month and I post it in the last day of the month Under NOPRODUCT & NOCUSTMER ( TARGET ) on the value date in %DAILYTIME_SET%. We don't need to have it in the Logical. Version is Actual, Customer = ALL, DATASOURCE = the value in *XDIM,

The DATASOURCE = PIFCO if we put it in *XDIM_MEMBERSET do we need to have it too in the logical ?

For the TARGET is OK what you put in BOLD will be the target. The DAILYTIME%DAILYTIME_SET% is a target date need to be included in TARGET.

Hope I answered all the question

Hi Federic,

I completely do not understand the logic with TIME dimension, but lets assume you have some time members equal for source, test and target!

*XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET CUSTOMER = < ALL >

*XDIM_MEMBERSET PRODUCT = < ALL >

*XDIM_MEMBERSET ACCOUNT= VCONC_W

*XDIM_MEMBERSET DATATYPE = VOLUME

*XDIM_MEMBERSET DATASOURCE = PIFCO

*XDIM_MEMBERSET DAILYTIME=something

*WHEN CUSTOMER

* IS *

*REC(EXPRESSION = ([ACCOUNT].[VCONC_H20],[DATATYPE].[PERCENT]) == 0 ?

([ACCOUNT].[VCONC_W_SUM_CALC],[PRODUCT].[NOPRODUCT],[DATASOURCE].[AUTOCALC],[CUSTOMER].[NOCUSTOMER]) : %VALUE%,ACCOUNT = VCONC_W_SUM_CALC, DATASOURCE = AUTOCALC, CUSTOMER = NOCUSTOMER, PRODUCT = NOPRODUCT)

*ENDWHEN

Hope it's clear!

Vadim

Hi Vadim thanks for your help and your time. When I try to validate your code in UJKT I received error Invalide WHEN/ENDWHEN - RIGHT ")" missing in *REC

I try to figure what is missing but I don't see

Please, show the code you validate in UJKT. And provide your BPC version and core SP.

Vadim

*XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET CUSTOMER =<ALL>

*XDIM_MEMBERSET PRODUCT = <ALL>

*XDIM_MEMBERSET ACCOUNT= VCONC_W

*XDIM_MEMBERSET DATATYPE = VOLUME

*XDIM_MEMBERSET DATASRC = PIFCO

*WHEN CUSTOMER

*IS *

*REC(EXPRESSION = ([ACCOUN].[VCONC_H20],[DATATYPE].[PERCENT])== 0 ?

([ACCOUNT].[VCONC_W_SUM_CALC],[PRODUCT].[NOPRODUCT],[DATASRC].[AUTOCALC],[CUSTOMER].[NOCUSTOMER])) : ACCOUNT = VCONC_W_SUM_CALC, DATASRC = AUTOCALC, CUSTOMER = NO_CUSTOMER2, PRODUCT = NOPRODUCT)

UJK_VALIDATION_EXCEPTION:Invalid when/endwhen - Right ")" missing in *REC

I'm on BPC 10 version SP7

Sorry Frederic,

But your code is absolutely different from what I post! The correct code for REC is:

*REC(EXPRESSION = ([ACCOUNT].[VCONC_H20],[DATATYPE].[PERCENT]) == 0 ?

([ACCOUNT].[VCONC_W_SUM_CALC],[PRODUCT].[NOPRODUCT],[DATASOURCE].[AUTOCALC],[CUSTOMER].[NO_CUSTOMER2]) : %VALUE%,ACCOUNT = VCONC_W_SUM_CALC, DATASOURCE = AUTOCALC, CUSTOMER = NO_CUSTOMER2, PRODUCT = NOPRODUCT)

Vadim

Sorry I copied wrong code. But With your code I still have same error. I just change Customer Account and rename DATASRC instead of Datasource

*XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET CUSTOMER = <ALL>

*XDIM_MEMBERSET PRODUCT = <ALL>

*XDIM_MEMBERSET ACCOUNT= VCONC_W

*XDIM_MEMBERSET DATATYPE = VOLUME

*XDIM_MEMBERSET DATASRC = PIFCO

*WHEN CUSTOMER

*IS *

*REC(EXPRESSION = ([ACCOUNT].[VCONC_H20],[DATATYPE].[PERCENT])== 0 ? ([ACCOUNT].[VCONC_W_SUM_CALC],[PRODUCT].[NOPRODUCT],[DATASRC].[AUTOCALC],[CUSTOMER].[NO_CUSTOMER2]) : %VALUE%, ACCOUNT = VCONC_W_SUM_CALC, DATASRC = AUTOCALC, CUSTOMER = NO_CUSTOMER2, PRODUCT = NOPRODUCT)

*ENDWHEN

*COMMIT

1. Remove COMMIT (and never use it!)

2. "I'm on BPC 10 version SP7" - can you show you BW version and SP? Like:

3. Sometimes REC expression is sensitive for extra spaces. Remove all! For test I was able to successfully validate the following test REC in my system (without spaces!)!

*REC(EXPRESSION=([PERIODS].[2012.FEB],[TITLES].[3100])==0?([PLANTYPE].[LE01],[CORPDIR].[CORPORATE]):%VALUE%,PLANTYPE=LE01,CORPDIR=CORPORATE)

Vadim

P.S. Even with more dimensions in target:

*REC(EXPRESSION=([PERIODS].[2012.FEB],[TITLES].[3100])==0?([PLANTYPE].[LE01],[CORPDIR].[CORPORATE],[BE].[BE3000]):%VALUE%,PLANTYPE=LE01,CORPDIR=CORPORATE,BE=BE3000)

IS ok now they have something aftet the ? ( extra space ). I will able to test now the result

Extra spaces after "(" and before ")" for the tuple expression are incorrect and resulted in error

Incorrect:

( [DIMNAME1].[MEM1],[DIMNAME2].[MEM2] )

Correct:

([DIMNAME1].[MEM1],[DIMNAME2].[MEM2])

Is it possible to answer the question: "2. "I'm on BPC 10 version SP7" - can you show you BW version and SP? Like: How To: Ask questions about Script Logic issues"

By the way, for test select some fixed TIME period...

Vadim

I removed all extra spaces and I still have issue I removed extra space after ==0? and it's work but now I need to check the script because I have no result with when COMMIT

We're on BW 7.4 Patch 6

"I have no result with when COMMIT" - what do you mean?

If the script is running - check that you have data in the cube!

Show the UJKT log of script run...

Vadim

If I used SIMULATION or IF I ADD *COMMIT

This is the result.

I have an ad hoc report to confirm if I have posting and I don't have a posting I send data on my 2 customers. See Attachment. I expected posting using Volume Linked to Code CSI ( line 10 & 13 )

LGX: *XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET CUSTOMER =

*XDIM_MEMBERSET PRODUCT =

*XDIM_MEMBERSET ACCOUNT= VCONC_W

*XDIM_MEMBERSET DATATYPE = VOLUME

*XDIM_MEMBERSET DATASRC = PIFCO

*WHEN CUSTOMER

*IS *

*REC(EXPRESSION = ([ACCOUNT].[VCONC_H20],[DATATYPE].[PERCENT])==0?([ACCOUNT].[VCONC_W_SUM_CALC],[PRODUCT].[NOPRODUCT],[DATASRC].[AUTOCALC],[CUSTOMER].[NOCUSTOMER]) : %VALUE%,ACCOUNT = VCONC_W_SUM_CALC, DATASRC = AUTOCALC,CUSTOMER=NO_CUSTOMER2,PRODUCT=NOPRODUCT) *ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG: FILE:\ROOT\WEBFOLDERS\AMMC \ADMINAPP\REVENUE\TEST.LGF USER:SOUCYF APPSET:AMMC APPLICATION:REVENUE [INFO] GET_DIM_LIST(): I_APPL_ID="REVENUE", #dimensions=13 ACCOUNT,COMPANY,CUSTOMER,DAILYTIME,DATASRC,DATATYPE,INPCURRENCY,MEASURES,PRODUCT,PROFITCENTER,RPTCURRENCY,TRANSPORT,VERSION #dim_memberset=5 DAILYTIME:2014.JAN,1 in total. VERSION:ACTUAL,1 in total. ACCOUNT:VCONC_W,1 in total. DATATYPE:VOLUME,1 in total. DATASRC:PIFCO,1 in total. SCRIPT RUNNING TIME IN TOTAL:1.00 s.

On the report I don't see data for result:

ACCOUNT = VCONC_W_SUM_CALC

CUSTOMER=NO_CUSTOMER2

PRODUCT=NOPRODUCT

DATASRC = AUTOCALC

Start testing simple things:

First - do you have data in VOLUME?

*XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET DAILYTIME=2014.JAN

*XDIM_MEMBERSET ACCOUNT= VCONC_W

*XDIM_MEMBERSET DATATYPE = VOLUME

*XDIM_MEMBERSET DATASRC = PIFCO

*WHEN CUSTOMER

*IS *

*REC(EXPRESSION = %VALUE%) // no change - write same value

*ENDWHEN

Execute and copy written members

Second - do you have related data in PERCENT

*XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET DAILYTIME=2014.JAN

*XDIM_MEMBERSET ACCOUNT= VCONC_H20

*XDIM_MEMBERSET DATATYPE = PERCENT

*XDIM_MEMBERSET DATASRC = PIFCO

*WHEN CUSTOMER

*IS *

*REC(EXPRESSION = %VALUE%) // no change - write same value

*ENDWHEN

Execute and copy written members

Compare all dimensions!

P.S. NEVER ADD COMMIT!!!

Vadim I added the

*XDIM_MEMBERSET DAILYTIME = BAS(2014.JAN) // To have all members of JAN 2014

Now it's working the volume of 1000 is copied into VCONC_W_SUM_CALC, NO_CUSTOMER2, NOPRODUCT, AUTOCALC.

I'll Continue my testing.

For my understanding why you said NEVER ADD COMMIT ???

Ok, BAS(2014.JAN) is required! Or you can set only one day for test.

"For my understanding why you said NEVER ADD COMMIT" - COMMIT is useful ONLY for MDX syntax in script logic. For so called SQL syntax with WHEN/ENDWHEN COMMIT is doing nothing. When WHEN/ENDWHEN loop is finished - autocommit always happens (you can look on ABAP code ). But COMMIT statement has a negative side effect - it will reset scope to the original script scope. As a result - a lot of errors happen if you add code after COMMIT. Simply don't use it!

Ask a Question

Top Q&A Solution Author

User

Count