Conditional *REC statement based on Value in Other Account

[Former Member]

Dear Experts,

I am trying the below mentioned Script logic but its not working as required.

*XDIM_MEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET RPTCURRENCY = INR
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET FLOW = F_999
*XDIM_MEMBERSET SCOPE = %SCOPE_SET%
*XDIM_MEMBERSET ENTITY = %ENTITY_SET%
*XDIM_MEMBERSET AUDITTRAIL = AJ_SHARE,INPUT_GW, AJ_CAPIN
*XDIM_MEMBERSET ACCOUNT = ELIM_GW, LC_GW

*WHEN ACCOUNT 
*IS ELIM_GW
*WHEN AUDITTRAIL
*IS AJ_SHARE
*REC(EXPRESSION = [ACCOUNT].[LC_GW];[AUDITRAIL].[INPUT_GW]==0 ? %VALUE%:0, 
AUDITTRAIL= AJ_CAPIN, ACCOUNT = ELIM_GW)
*ELSE
*ENDWHEN
*COMMIT

Basically, What I need the script to do is analyse each entity to see if the value in combination of Account: LC_GW and AUDITTRAIL: INPUT_GW is "0" then Write back the Value in combination of Account: ELIM_GW and Audittrail:AJ_SHARE to Combination of Audittrail:AJ_CAPIN and Account:ELIM_GW or if the Value in combination of Account: LC_GW and AUDITTRAIL: INPUT_GW is not "0" then Write back "0" to Combination of Audittrail:AJ_CAPIN and Account:ELIM_GW.

Is there any correction required in the Script?

Thanks,

Collin Mendoza.

[former_member186338 · Accepted solution]

Syntax of tuple is incorrect here:

*REC(EXPRESSION = [ACCOUNT].[LC_GW];[AUDITRAIL].[INPUT_GW]==0 ? %VALUE%:0, 
AUDITTRAIL= AJ_CAPIN, ACCOUNT = ELIM_GW)

Have to be:

*REC(EXPRESSION = ([ACCOUNT].[LC_GW],[AUDITRAIL].[INPUT_GW])==0 ? %VALUE%:0, 
AUDITTRAIL= AJ_CAPIN, ACCOUNT = ELIM_GW)

[Former Member · Accepted solution]

Dear Vadim, Below is a report which shows that for Account: LC_GW, Audittrail:INPUT_GW there is value in Entity 8000 but no value in Entity 8100. Actual INR 2016.MAR F_999 C_SCOPE LC_GW INPUT_GW 8000 1148523213 8100 I am Running the below Logic script in UJKT: *XDIM_MEMBERSET CATEGORY = ACTUAL *XDIM_MEMBERSET RPTCURRENCY = INR *XDIM_MEMBERSET TIME = 2016.MAR *XDIM_MEMBERSET FLOW = F_999 *XDIM_MEMBERSET SCOPE = C_SCOPE *XDIM_MEMBERSET ENTITY = 8000,8100 *XDIM_MEMBERSET AUDITTRAIL = AJ_SHARES *XDIM_MEMBERSET ACCOUNT = ELIM_GW *WHEN ACCOUNT *IS * *REC(EXPRESSION =([ACCOUNT].[LC_GW],[AUDITTRAIL].[INPUT_GW])==0 ? %VALUE%:0, AUDITTRAIL= AJ_CAPIN, ACCOUNT = ELIM_GW) *ENDWHEN I am Getting the Below result : LGX: *XDIM_MEMBERSET CATEGORY = ACTUAL *XDIM_MEMBERSET RPTCURRENCY = INR *XDIM_MEMBERSET TIME = 2016.MAR *XDIM_MEMBERSET FLOW = F_999 *XDIM_MEMBERSET SCOPE = C_SCOPE *XDIM_MEMBERSET ENTITY = 8000,8100 *XDIM_MEMBERSET AUDITTRAIL = AJ_SHARES *XDIM_MEMBERSET ACCOUNT = ELIM_GW *WHEN ACCOUNT *IS * *REC(EXPRESSION =([ACCOUNT].[LC_GW],[AUDITTRAIL].[INPUT_GW])==0 ? %VALUE%:0, AUDITTRAIL= AJ_CAPIN, ACCOUNT = ELIM_GW) *ENDWHEN ------------------------------------------------------------------------------------------------------------------------------------- LOG: LOG BEGIN TIME:2017-07-28 14:50:08 FILE:\ROOT\WEBFOLDERS\Test_Bkp \ADMINAPP\Consolidation\TEST.LGF USER:TESTUSER APPSET:Test_Bkp APPLICATION:Consolidation [INFO] GET_DIM_LIST(): I_APPL_ID="Consolidation", #dimensions=13 ACCOUNT,AUDITTRAIL,CATEGORY,ENTITY,FLOW,INTERCO,INTERSEG,MEASURES,RPTCURRENCY,SCOPE,SEGMENT,TIME,TRANSCURRENCY #dim_memberset=8 CATEGORY:ACTUAL,1 in total. RPTCURRENCY:INR,1 in total. TIME:2016.MAR,1 in total. FLOW:F_999,1 in total. SCOPE:C_SCOPE,1 in total. ENTITY:8000,8100,2 in total. AUDITTRAIL:AJ_SHARES,1 in total. ACCOUNT:ELIM_GW,1 in total. REC :([ACCOUNT].[LC_GW],[AUDITTRAIL].[INPUT_GW])==0 ? %VALUE%:0 CALCULATION BEGIN: QUERY PROCESSING DATA QUERY TIME : 1.00 ms. 3 RECORDS QUERIED OUT. QUERY REFERENCE DATA QUERY TIME : 0.00 ms. 1 RECORDS QUERIED OUT. CALCULATION TIME IN TOTAL :0.00 ms. 3 RECORDS ARE GENERATED. CALCULATION END. ENDWHEN ACCUMULATION: 3 RECORDS ARE GENERATED. DATA TO WRITE BACK: ACCOUNT AUDITTRAIL CATEGORY ENTITY FLOW INTERCO INTERSEG RPTCURRENCY SCOPE SEGMENT TIME TRANSCURRENCY SIGNEDDATA ELIM_GW AJ_CAPIN ACTUAL 8000 F_999 I_1000 I_NO_SEG INR C_SCOPE NO_SEG 2016.MAR NO_TC 2666260340.00 ELIM_GW AJ_CAPIN ACTUAL 8100 F_999 I_8000 I_NO_SEG INR C_SCOPE NO_SEG 2016.MAR NO_TC 859838242.31 ELIM_GW AJ_CAPIN ACTUAL 8100 F_999 I_8000 I_NO_SEG INR C_SCOPE PHARMA 2016.MAR NO_TC 16398112.50 3 RECORDS HAVE BEEN WRITTEN BACK. WRITING TIME :1.00 ms. SCRIPT RUNNING TIME IN TOTAL:2.00 s. LOG END TIME:2017-07-28 14:50:09 ________________________________________________________________________________________ As you can see it is generating value for both Entity 8000 as well as 8100. Whereas required output is it should generate value only for 8100 since for Entity 8100 LC_GW is 0 and Not generate value for Entity 8000 since LC_GW is 1148523213. Any clue what could be the reason? Thanks, Collin.

[former_member186338 · Accepted solution]

Completely unreadable!

[Former Member]

Dear Vadim,

I made the below change to the Script now it is working as required.

*XDIM_MEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET RPTCURRENCY = INR
*XDIM_MEMBERSET TIME = 2016.MAR
*XDIM_MEMBERSET FLOW = F_999
*XDIM_MEMBERSET SCOPE = C_SCOPE
*XDIM_MEMBERSET ENTITY = 8000,8100
*XDIM_MEMBERSET AUDITTRAIL = AJ_SHARE
*XDIM_MEMBERSET ACCOUNT = ELIM_GW

*WHEN ACCOUNT 
*IS *
*REC(EXPRESSION =([ACCOUNT].[LC_GW],[AUDITTRAIL].[INPUT_GW],[INTERCO].[ALL_TOT],[TRANSCURRENCY].[ALL_TR],[INTERSEG].[TOT],[SEGMENT].[S_TOT])==0 ? %VALUE%:0, AUDITTRAIL= AJ_CAPIN, ACCOUNT = ELIM_GW)
*ENDWHEN

Thanks for the help. 🙂

Collin.

[former_member186338]

Some other small issues, the code has to be:

*XDIM_MEMBERSET AUDITTRAIL = AJ_SHARE // don't scope not used!
*XDIM_MEMBERSET ACCOUNT = ELIM_GW  // don't scope not used!
*WHEN ACCOUNT 
*IS *
*REC(EXPRESSION = ([ACCOUNT].[LC_GW],[AUDITRAIL].[INPUT_GW])==0 ? %VALUE%:0, 
AUDITTRAIL= AJ_CAPIN, ACCOUNT = ELIM_GW)
*ENDWHEN

And don't use useless COMMIT!

[Former Member]

Dear Vadim, I got a notification that you have left a comment on my post but I am neither able to see my post neither your comment. 😞 Thanks, Collin.

[former_member186338]

Refresh the page:) and click on "Comments"

[Former Member]

Dear Vadim,

Above is a report showing Entity 8000 has value in Account LC_GW but Entity 8100 has no value.

I am trying the below script in UJKT:

*XDIM_MEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET RPTCURRENCY = INR
*XDIM_MEMBERSET TIME = 2016.MAR
*XDIM_MEMBERSET FLOW = F_999
*XDIM_MEMBERSET SCOPE = C_SCOPE
*XDIM_MEMBERSET ENTITY = 8000,8100
*XDIM_MEMBERSET AUDITTRAIL = AJ_SHARES
*XDIM_MEMBERSET ACCOUNT = ELIM_GW
*WHEN ACCOUNT 
*IS *
*REC(EXPRESSION =([ACCOUNT].[LC_GW],[AUDITTRAIL].[INPUT_GW])==0 ? %VALUE%:0, AUDITTRAIL= AJ_CAPIN, ACCOUNT = ELIM_GW)
*ENDWHEN

I am getting the below results:

LGX:

*XDIM_MEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET RPTCURRENCY = INR
*XDIM_MEMBERSET TIME = 2016.MAR
*XDIM_MEMBERSET FLOW = F_999
*XDIM_MEMBERSET SCOPE = C_SCOPE
*XDIM_MEMBERSET ENTITY = 8000,8100
*XDIM_MEMBERSET AUDITTRAIL = AJ_SHARES
*XDIM_MEMBERSET ACCOUNT = ELIM_GW
*WHEN ACCOUNT
*IS *
*REC(EXPRESSION =([ACCOUNT].[LC_GW],[AUDITTRAIL].[INPUT_GW])==0 ? %VALUE%:0, AUDITTRAIL= AJ_CAPIN, ACCOUNT = ELIM_GW)
*ENDWHEN
-------------------------------------------------------------------------------------------------------------------------------------
LOG:

LOG BEGIN TIME:2017-07-28 14:50:08
FILE:\ROOT\WEBFOLDERS\Test_Bkp \ADMINAPP\Consolidation\TEST.LGF
USER:TESTUSER
APPSET:Test_Bkp
APPLICATION:Consolidation
[INFO] GET_DIM_LIST(): I_APPL_ID="Consolidation", #dimensions=13 
ACCOUNT,AUDITTRAIL,CATEGORY,ENTITY,FLOW,INTERCO,INTERSEG,MEASURES,RPTCURRENCY,SCOPE,SEGMENT,TIME,TRANSCURRENCY

#dim_memberset=8 
CATEGORY:ACTUAL,1 in total.
RPTCURRENCY:INR,1 in total.
TIME:2016.MAR,1 in total.
FLOW:F_999,1 in total.
SCOPE:C_SCOPE,1 in total.
ENTITY:8000,8100,2 in total.
AUDITTRAIL:AJ_SHARES,1 in total.
ACCOUNT:ELIM_GW,1 in total.

REC :([ACCOUNT].[LC_GW],[AUDITTRAIL].[INPUT_GW])==0 ? %VALUE%:0

CALCULATION BEGIN:
QUERY PROCESSING DATA
QUERY TIME : 1.00 ms. 3  RECORDS QUERIED OUT.
QUERY REFERENCE DATA
QUERY TIME : 0.00 ms. 1  RECORDS QUERIED OUT.
CALCULATION TIME IN TOTAL :0.00 ms.
3  RECORDS ARE GENERATED.
CALCULATION END.

ENDWHEN ACCUMULATION: 3  RECORDS ARE GENERATED.
DATA TO WRITE BACK:
ACCOUNT	AUDITTRAIL	CATEGORY	ENTITY	FLOW	INTERCO	INTERSEG	RPTCURRENCY	SCOPE	SEGMENT	TIME	TRANSCURRENCY	SIGNEDDATA
ELIM_GW	AJ_CAPIN	ACTUAL	8000	F_999	I_1000	I_NO_SEG	INR	C_SCOPE	NO_SEG	2016.MAR	NO_TC	2666260340.00 
ELIM_GW	AJ_CAPIN	ACTUAL	8100	F_999	I_8000	I_NO_SEG	INR	C_SCOPE	NO_SEG	2016.MAR	NO_TC	859838242.31 
ELIM_GW	AJ_CAPIN	ACTUAL	8100	F_999	I_8000	I_NO_SEG	INR	C_SCOPE	PHARMA	2016.MAR	NO_TC	16398112.50 
3  RECORDS HAVE BEEN WRITTEN BACK.
WRITING TIME :1.00  ms.

SCRIPT RUNNING TIME IN TOTAL:2.00 s.
LOG END TIME:2017-07-28 14:50:09

As you can see it is generating value for both Entity 8000 as well as 8100.

Whereas required output is it should generate value only for 8100 since for Entity 8100 LC_GW is 0 and Not generate value for Entity 8000 since LC_GW is 1148523213.

Any clue what could be the reason?

Thanks, Collin.

[former_member186338]

Please, post something readable! I will not provide any help when the information is presented like in your posts. Sorry!

[former_member186338]

Not all dimensions are shown on the report!

INTERCO,INTERSEG,SEGMENT,TRANSCURRENCY - missing!

Unable to comment without correct report!