Logic script: reading value when empty or 0

[a_ahmad]

Hi,

-we are on CPMBPC release: 810 SP-level: 0004

The requirement is to read data from 'January to November' depending on where value is and write on 'December'.

So, script reads data in JAN and if there is value then writes on DEC but if value=0 or empty then move to FEB and so on..........until it finds value.

How is it possible to read when value is empty or 0?

thanks

Ahmad

[former_member186338 · Accepted solution]

Like this:

MAR: 12 to be written to DEC?

[a_ahmad · Accepted solution]

thanks Vadim, you interpreted it correctly. / regards / Ahmad

[former_member186338 · Accepted solution]

Then you have to loop from NOV to JAN (opposite direction)! And simply skip zero's!

Something like:

Let's assume that in %Y% we have Year to process. Then:

*FOR %M%=%Y%.NOV,%Y%.OCT,%Y%.SEP,%Y%.AUG,...

//or *FOR %M%=%Y%.11,%Y%.10,%Y%.09,%Y%.08,...

*XDIM_MEMBERSET TIME=%M% //scope single month in the loop

*WHEN TIME

*IS *

*REC(EXPRESSION=(%VALUE%==0) ? [TIME].[%Y%.DEC] : %VALUE%, TIME=%Y%.DEC)

//or *REC(EXPRESSION=(%VALUE%==0) ? [TIME].[%Y%.12] : %VALUE%, TIME=%Y%.12)

*ENDWHEN

*NEXT

In REC if the current value is zero then DEC value will be written over DEC - no change!

For empty records nothing will be written automatically.

Vadim

[a_ahmad · Accepted solution]

NOV to JAN is cool idea.

Q1.

when i run package for this purpose, for TIME I want to choose 01.2013 .........12.2015 as this should work for Current year - 2. Will this script work?

Q2.

Sorry but i need to ask how to declare %Y%

sample script:

*XDIM_MEMBERSET ACC = 1110000

*XDIM_MEMBERSET CCODE = C-9500

*XDIM_MEMBERSET PARTNER = <ALL>

*XDIM_MEMBERSET MOVEMENT = 3100

*XDIM_MEMBERSET DATASRC = TECH1

*XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET RPTCURRENCY = LC

*FOR %M%=%Y%.11,%Y%.10,%Y%.09,%Y%.08,%Y%.07,%Y%.06,%Y%.05,%Y%.04,%Y%.03,%Y%.02,%Y%.01

*XDIM_MEMBERSET TIME=%M%

*WHEN TIME

*IS *

*REC(EXPRESSION=(%VALUE%==0) ? [TIME].[%Y%.12] : %VALUE%, TIME=%Y%.12)

*ENDWHEN

*NEXT

[former_member186338 · Accepted solution]

Q: What do you want to ask from user?

Current year?

[a_ahmad · Accepted solution]

In data preparation stage, this step is administrated by one person manually once a year.

Current year - 2

source data periods: 01 to 11

target data period: 12

So, Admin user for example in 2015 chooses, 01.2013 ..........12.2015.

[former_member186338 · Accepted solution]

If Current year, then:

In the advanced DM script add the following (add as many years as you want):

PROMPT(COMBOBOX,%Y%,"Select BU to run earning calculations",0,,{2014,2015,2016,2017,2018})

INFO(%EQU%,=)

INFO(%TAB%,;)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,EQU,%EQU%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,TAB,%TAB%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM,Y%EQU%%Y%)

...

As a result, the variable $Y$ will be passed to the script logic containing user selected year.

Then in the script logic:

*SELECT(%Y2014%,[ID],TIME,"[ID]=2012.01,2013.01,2014.01")

*SELECT(%Y2015%,[ID],TIME,"[ID]=2013.01,2014.01,2015.01")

*SELECT(%Y2016%,[ID],TIME,"[ID]=2014.01,2015.01,2016.01")

*SELECT(%Y2017%,[ID],TIME,"[ID]=2015.01,2016.01,2017.01")

*SELECT(%Y2018%,[ID],TIME,"[ID]=2016.01,2017.01,2018.01")

//add more as required

*SELECT(%YY%,[YEAR],TIME,"[ID]=%Y$Y$%")

*FOR %Y%=%YY%

*FOR %M%=%Y%.11,%Y%.10,%Y%.09,%Y%.08,%Y%.07,%Y%.06,%Y%.05,%Y%.04,%Y%.03,%Y%.02,%Y%.01

*XDIM_MEMBERSET TIME=%M%

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[%Y%.12] : %VALUE%, TIME=%Y%.12)

*ENDWHEN

*NEXT

*NEXT

Vadim

[former_member186338 · Accepted solution]

Result in UJKT with PARAM: Y=2016 ($Y$=2016):

EXECUTE(Simulate):

LGX:

*XDIM_MEMBERSET TIME=2014.11

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2014.12] : %VALUE%, TIME=2014.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2014.10

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2014.12] : %VALUE%, TIME=2014.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2014.09

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2014.12] : %VALUE%, TIME=2014.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2014.08

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2014.12] : %VALUE%, TIME=2014.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2014.07

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2014.12] : %VALUE%, TIME=2014.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2014.06

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2014.12] : %VALUE%, TIME=2014.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2014.05

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2014.12] : %VALUE%, TIME=2014.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2014.04

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2014.12] : %VALUE%, TIME=2014.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2014.03

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2014.12] : %VALUE%, TIME=2014.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2014.02

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2014.12] : %VALUE%, TIME=2014.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2014.01

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2014.12] : %VALUE%, TIME=2014.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2015.11

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2015.12] : %VALUE%, TIME=2015.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2015.10

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2015.12] : %VALUE%, TIME=2015.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2015.09

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2015.12] : %VALUE%, TIME=2015.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2015.08

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2015.12] : %VALUE%, TIME=2015.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2015.07

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2015.12] : %VALUE%, TIME=2015.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2015.06

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2015.12] : %VALUE%, TIME=2015.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2015.05

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2015.12] : %VALUE%, TIME=2015.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2015.04

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2015.12] : %VALUE%, TIME=2015.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2015.03

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2015.12] : %VALUE%, TIME=2015.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2015.02

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2015.12] : %VALUE%, TIME=2015.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2015.01

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2015.12] : %VALUE%, TIME=2015.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2016.11

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2016.12] : %VALUE%, TIME=2016.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2016.10

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2016.12] : %VALUE%, TIME=2016.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2016.09

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2016.12] : %VALUE%, TIME=2016.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2016.08

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2016.12] : %VALUE%, TIME=2016.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2016.07

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2016.12] : %VALUE%, TIME=2016.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2016.06

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2016.12] : %VALUE%, TIME=2016.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2016.05

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2016.12] : %VALUE%, TIME=2016.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2016.04

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2016.12] : %VALUE%, TIME=2016.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2016.03

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2016.12] : %VALUE%, TIME=2016.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2016.02

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2016.12] : %VALUE%, TIME=2016.12)

*ENDWHEN

*XDIM_MEMBERSET TIME=2016.01

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[2016.12] : %VALUE%, TIME=2016.12)

*ENDWHEN

Vadim

[a_ahmad · Accepted solution]

in UJKT, I got the same results as yours.

Package: But when i ran the package, I got error:

SELECTION = /TEMP/MPLAN/PRIVATEPUBLICATIONS/AHMAD/TempFiles/FROM.TMP@@@SAVE@@@@@@EXPAND@@@|DIMENSION:CCODE|9500|DIMENSION:VERSION|ACT

Y = 2015

[Message]

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

RUN_LOGIC:Dimension set:"TIME" not assigned in Data Manager

model: MPLAN. Package status: ERROR

in Package / advanced:

PROMPT(SELECT,,,,"%CATEGORY_DIM%,%ENTITY_DIM%")

PROMPT(COMBOBOX,%Y%,"Select year",0,,{2014,2015,2016,2017,2018})

INFO(%EQU%,=)

INFO(%TAB%,;)

TASK(/CPMB/RUNCALCACCOUNT_LOGIC,EQU,%EQU%)

TASK(/CPMB/RUNCALCACCOUNT_LOGIC,TAB,%TAB%)

TASK(/CPMB/RUNCALCACCOUNT_LOGIC,REPLACEPARAM,Y%EQU%%Y%)

TASK(/CPMB/RUNCALCACCOUNT_LOGIC,SUSER,%USER%)

TASK(/CPMB/RUNCALCACCOUNT_LOGIC,SAPPSET,%APPSET%)

TASK(/CPMB/RUNCALCACCOUNT_LOGIC,SAPP,%APP%)

TASK(/CPMB/RUNCALCACCOUNT_LOGIC,SELECTION,%SELECTION%)

TASK(/CPMB/RUNCALCACCOUNT_LOGIC,LOGICFILENAME,Z_OPE_CALC1.LGF)

[former_member186338 · Accepted solution]

"RUN_LOGIC:Dimension set:"TIME" not assigned in Data Manager" - please provide the script text of Z_OPE_CALC1.LGF. The DM advanced script looks like correct.

Vadim

P.S. Looks like you use %TIME_SET%?

[a_ahmad · Accepted solution]

Hi Vadim,

Yes, you are right. In first part of the script I need to clear data from period 12 before reading from JAN to NOV. There I was using TIME_SET. As I replaced it with a variable, the script is giving required result.

Thanks for your help!!!

Best regards

Ahmad

[former_member186338 · Accepted solution]

Something like:

*SELECT(%Y2014%,[ID],TIME,"[ID]=2012.01,2013.01,2014.01")

*SELECT(%Y2015%,[ID],TIME,"[ID]=2013.01,2014.01,2015.01")

*SELECT(%Y2016%,[ID],TIME,"[ID]=2014.01,2015.01,2016.01")

*SELECT(%Y2017%,[ID],TIME,"[ID]=2015.01,2016.01,2017.01")

*SELECT(%Y2018%,[ID],TIME,"[ID]=2016.01,2017.01,2018.01")

*SELECT(%YY%,[YEAR],TIME,"[ID]=%Y$Y$%")

*FOR %Y%=%YY%

*XDIM_MEMBERSET TIME=%Y%.12

*WHEN TIME

*IS *

*REC(EXPRESSION=0)

*ENDWHEN

*FOR %M%=%Y%.11,%Y%.10,%Y%.09,%Y%.08,%Y%.07,%Y%.06,%Y%.05,%Y%.04,%Y%.03,%Y%.02,%Y%.01

*XDIM_MEMBERSET TIME=%M%

*WHEN TIME

*IS *

*REC(EXPRESSION= %VALUE%==0 ? [TIME].[%Y%.12] : %VALUE%, TIME=%Y%.12)

*ENDWHEN

*NEXT

*NEXT

Vadim

[former_member186338]

Let me check the logic:

You want to check value in the loop JAN-NOV and when non zero/non empty value is found - STOP the loop and write it to DEC?

Vadim