Skip to Content
0

Conditional Records based on dimension property

Nov 29, 2017 at 10:50 PM

52

avatar image
Former Member

Hi Guys

I want to create a record based on the total amount of all records generated by the property DIMLIST = "CXC_CXP" of the ACCOUNT dimension. If that total is negative, the account will be 123456789, if it is positive the account will be 987654321.

For Example: I have these records

ACCOUNT | DIMLIST | ENTITY | INTCO | SCOPE | CURRENCY | AMOUNT

11302300 | CXC_CXP | GL9996 | IGL9999 | CG_COMPANYRT | USD | 75

21120100 | CXC_CXP | GL9996 | IGLE600 | CG_COMPANYRT | USD | -25

13456988 | | GL9996 | IGL9999 | CG_COMPANYRT | USD | 50

The total amount of the records with property DIMLIST = "CXC_CXP" is 50. In ths case, the total its positive, so the new record will be on the account 987654321.

I made this code:

*XDIM_MEMBERSET TIME = 2017.09
*XDIM_MEMBERSET ENTITY = GL9996
*XDIM_FILTER ACCOUNT = [account].Properties("DIMLIST") = CXC_CXP
*XDIM_MEMBERSET CURRENCY = USD
*XDIM_MEMBERSET SCOPE = CG_COMPANYRT

*WHEN ENTITY
*IS GL9996
*REC(EXPRESSION=(%VALUE% > 0) ? %VALUE%:0, ACCOUNT=987654321, INTCO=IGL9999)


*REC(EXPRESSION=(%VALUE% < 0) ? %VALUE%:0, ACCOUNT=123456789, INTCO=IGL9999)
*ENDWHEN

but this code create a record for each one of the records in the scoped, so thats not what i wanted, im only want one record in the correct account depending of its positive or negative.

Can you tell me what can i add / modify o what statements i have to use for this?

Thanks for the help and best regards.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Vadim Kalinin Nov 30, 2017 at 07:37 AM
0

Use 2 steps:

In the first step aggregate all data in the first account:

*XDIM_MEMBERSET TIME = 2017.09
*XDIM_MEMBERSET ENTITY = GL9996
*XDIM_FILTER ACCOUNT = [account].Properties("DIMLIST") = CXC_CXP
*XDIM_MEMBERSET CURRENCY = USD
*XDIM_MEMBERSET SCOPE = CG_COMPANYRT

*WHEN ENTITY
*IS *
*REC(EXPRESSION=%VALUE%, ACCOUNT=987654321, INTCO=IGL9999)
*ENDWHEN

In the second - move data if negative!

*XDIM_MEMBERSET ACCOUNT=987654321
*XDIM_MEMBERSET INTCO=IGL9999

*WHEN ENTITY
*IS *
*REC(EXPRESSION=(%VALUE% < 0) ? %VALUE%:0, ACCOUNT=123456789)
*REC(EXPRESSION=(%VALUE% < 0) ? 0:%VALUE%, ACCOUNT=987654321)
*ENDWHEN
Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Vadim thanks for your helpful answer.

I did as you mention, i made it in two steps, but i face the problem that the account asignation is based on the value of the single record not by the total value. Let me explain more in detail

I have the following set records

TIME|ACCOUNT|DIMLIST|ENTITY|VALUE|

2017.09|11130102|SERV_ADM|GL8002|-1,319,582.59

2017.09|11130200|CXC_CXP|GL8000|-26,066,726.36

2017.09|11130200|CXC_CXP|GL8002|-115,982,288.51

2017.09|11130210|CXC_CXP|GLE400|-44,483.68

2017.09|21120100|CXC_CXP|GL8000|75,655,820.50

2017.09|21120100|CXC_CXP|GL8002|66,393,194.36

2017.09|21200243|SERV_ADM|GL8000|1,319,582.58

I need only the CXC_CXP DIMLITS Records, to do this, i made this code

*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET SCOPE = %SCOPE_SET%

//Select Data From Scope Dimension
*WHEN SCOPE
*IS CG_COMPANYRT 

	*XDIM_MEMBERSET ENTITY = BAS(G_COMPANYRT)
	*XDIM_FILTER ACCOUNT = [ACCOUNT].Properties("DIMLIST") = CXC_CXP
	*XDIM_MEMBERSET CURRENCY = USD
	*XDIM_MEMBERSET FLOW = FCLO
	*XDIM_MEMBERSET AUDITTRAIL = ELIMBALANCE
*ENDWHEN

This code will give me the following result:

TIME|ACCOUNT|DIMLIST|ENTITY|VALUE|

2017.09|11130200|CXC_CXP|GL8000|-26,066,726.36

2017.09|11130200|CXC_CXP|GL8002|-115,982,288.51

2017.09|11130210|CXC_CXP|GLE400|-44,483.68

2017.09|21120100|CXC_CXP|GL8000|75,655,820.50

2017.09|21120100|CXC_CXP|GL8002|66,393,194.36

The total of the values of this result is -44,483.69, this value i will save it in the account E_CXC_CXP for the future comparison, to do this i made this code:

// REC the Total value of the dimlist CXC_CXP in E_CXC_CXP account
*REC(FACTOR=1, ACCOUNT=E_CXC_CXP, INTERCO=IGLNOASSIGN, AUDITTRAIL=ELIMBALANCE_E, ENTITY=DUMMY)

With this code i will have a record like this

TIME|ACCOUNT|ENTITY|INTERCO|AUDITTRAIL|VALUE| 2017.09|E_CXC_CXP|DUMMY|IGLNOASSINGN|ELIMBALANCE_E|-44,483.69

Now, this is the part where i have issues. Depending if the total value is positive or negative, i will assign all the records to an account, if its positive all the records have to be in the account 11130200E, if the total value its negative, all the records will be in the 21020300E account. In this case, the total value is -44,483.69, so its negative and all the records have to be in the 21020300E account. To do this, i made this code

// If the Total value of the dimlist is > 0 REC all the records with in dimlist CXC_CXP in 11130200E account.
// If the Total value of the dimlist is < 0 REC all the records with in dimlist CXC_CXP in 21020300E account
*WHEN ENTITY
*IS *

*REC(EXPRESSION=(([TIME].[%TIME_SET%]/[ACCOUNT].[E_CXC_CXP]/[AUDITTRAIL].[ELIMBALANCE_E]/[ENTITY].[DUMMY]/%VALUE%) > 0) ? %VALUE% : 0,ACCOUNT=11130200E,AUDITTRAIL=ELIMBALANCE_E, INTERCO = IGLNOASSIGN)

*REC(EXPRESSION=(([TIME].[%TIME_SET%]/[ACCOUNT].[E_CXC_CXP]/[AUDITTRAIL].[ELIMBALANCE_E]/[ENTITY].[DUMMY]/%VALUE%) < 0) ? %VALUE% : 0,ACCOUNT=21020300E,AUDITTRAIL=ELIMBALANCE_E, INTERCO = IGLNOASSIGN)

*ENDWHEN

With this code the resutl is:

TIME|ACCOUNT|ENTITY|INTERCO|AUDITTRAIL|VALUE| 2017.09|21020300E|GL8000|IGLNOASSINGN|ELIMBALANCE_E|-26,066,726.36 2017.09|21020300E|GL8002|IGLNOASSINGN|ELIMBALANCE_E|-115,982,288.51 2017.09|21020300E|GLE400|IGLNOASSINGN|ELIMBALANCE_E|-44,483.68 2017.09|11130200E|GL8000|IGLNOASSINGN|ELIMBALANCE_E|75,655,820.50 2017.09|11130200E|GL8002|IGLNOASSINGN|ELIMBALANCE_E|66,393,194.36

but the correct one should be

TIME|ACCOUNT|ENTITY|INTERCO|AUDITTRAIL|VALUE| 2017.09|21020300E|GL8000|IGLNOASSINGN|ELIMBALANCE_E|-26,066,726.36 2017.09|21020300E|GL8002|IGLNOASSINGN|ELIMBALANCE_E|-115,982,288.51 2017.09|21020300E|GLE400|IGLNOASSINGN|ELIMBALANCE_E|-44,483.68 2017.09|21020300E|GL8000|IGLNOASSINGN|ELIMBALANCE_E|75,655,820.50 2017.09|21020300E|GL8002|IGLNOASSINGN|ELIMBALANCE_E|66,393,194.36

Can you help me with this part of the comparison?.

Thanks and Regards

0

Absolutely incorrect code:

*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET SCOPE = %SCOPE_SET%

//Select Data From Scope Dimension
*WHEN SCOPE
*IS CG_COMPANYRT 

	*XDIM_MEMBERSET ENTITY = BAS(G_COMPANYRT)
	*XDIM_FILTER ACCOUNT = [ACCOUNT].Properties("DIMLIST") = CXC_CXP
	*XDIM_MEMBERSET CURRENCY = USD
	*XDIM_MEMBERSET FLOW = FCLO
	*XDIM_MEMBERSET AUDITTRAIL = ELIMBALANCE
*ENDWHEN
0

I see that you want to check value aggregated by ACCOUNT DIMLIST=CXC_CXP, ENTITY and INTERCO:

//TIME|ACCOUNT|DIMLIST|ENTITY|INTERCO|AUDITTRAIL|VALUE|
//2017.09|11130102|SERV_ADM|GL8002|Any|ELIMBALANCE|-1,319,582.59
//2017.09|11130200|CXC_CXP|GL8000|Any|ELIMBALANCE|-26,066,726.36
//2017.09|11130200|CXC_CXP|GL8002|Any|ELIMBALANCE|-115,982,288.51
//2017.09|11130210|CXC_CXP|GLE400|Any|ELIMBALANCE|-44,483.68
//2017.09|21120100|CXC_CXP|GL8000|Any|ELIMBALANCE|75,655,820.50
//2017.09|21120100|CXC_CXP|GL8002|Any|ELIMBALANCE|66,393,194.36
//2017.09|21200243|SERV_ADM|GL8000|Any|ELIMBALANCE|1,319,582.58

*XDIM_MEMBERSET TIME = %TIME_SET% //2017.09
*XDIM_MEMBERSET SCOPE = CG_COMPANYRT
*XDIM_MEMBERSET ENTITY = BAS(G_COMPANYRT)
*XDIM_FILTER ACCOUNT = [ACCOUNT].Properties("DIMLIST") = CXC_CXP
*XDIM_MEMBERSET CURRENCY = USD
*XDIM_MEMBERSET FLOW = FCLO
*XDIM_MEMBERSET AUDITTRAIL = ELIMBALANCE

//After scoping:
//TIME|ACCOUNT|DIMLIST|ENTITY|INTERCO|AUDITTRAILVALUE|
//2017.09|11130200|CXC_CXP|GL8000|Any|ELIMBALANCE|-26,066,726.36
//2017.09|11130200|CXC_CXP|GL8002|Any|ELIMBALANCE|-115,982,288.51
//2017.09|11130210|CXC_CXP|GLE400|Any|ELIMBALANCE|-44,483.68
//2017.09|21120100|CXC_CXP|GL8000|Any|ELIMBALANCE|75,655,820.50
//2017.09|21120100|CXC_CXP|GL8002|Any|ELIMBALANCE|66,393,194.36

*WHEN ENTITY
*IS *
// All DIMLIST=CXC_CXP accounts will be saved to 11130200E, ALL INTERCO will go to IGLNOASSIGN
// All ENTITY will go to DUMMY
*REC(EXPRESSION=%VALUE%, ACCOUNT=E_CXC_CXP, AUDITTRAIL=ELIMBALANCE_E, INTERCO=IGLNOASSIGN, ENTITY=DUMMY)
*ENDWHEN

//Saved record:
//TIME|ACCOUNT|ENTITY|INTERCO|AUDITTRAIL|VALUE| 
//2017.09|E_CXC_CXP|DUMMY|IGLNOASSINGN|ELIMBALANCE_E|-44,483.69

// Checking tuple expression: ([ACCOUNT].[E_CXC_CXP],[AUDITTRAIL].[ELIMBALANCE_E],[ENTITY].[DUMMY],[INTERCO].[IGLNOASSIGN])
*WHEN ENTITY
*IS *
*REC(EXPRESSION=([ACCOUNT].[E_CXC_CXP],[AUDITTRAIL].[ELIMBALANCE_E],[ENTITY].[DUMMY],[INTERCO].[IGLNOASSIGN]) > 0) ? %VALUE% : 0, ACCOUNT=11130200E,AUDITTRAIL=ELIMBALANCE_E, INTERCO = IGLNOASSIGN)
*REC(EXPRESSION=([ACCOUNT].[E_CXC_CXP],[AUDITTRAIL].[ELIMBALANCE_E],[ENTITY].[DUMMY],[INTERCO].[IGLNOASSIGN]) < 0) ? %VALUE% : 0, ACCOUNT=21020300E,AUDITTRAIL=ELIMBALANCE_E, INTERCO = IGLNOASSIGN)
*ENDWHEN

//TIME|ACCOUNT|ENTITY|INTERCO|AUDITTRAIL|VALUE| 
//2017.09|21020300E|GL8000|IGLNOASSINGN|ELIMBALANCE_E|-26,066,726.36 
//2017.09|21020300E|GL8002|IGLNOASSINGN|ELIMBALANCE_E|-115,982,288.51 
//2017.09|21020300E|GLE400|IGLNOASSINGN|ELIMBALANCE_E|-44,483.68 
//2017.09|21020300E|GL8000|IGLNOASSINGN|ELIMBALANCE_E|75,655,820.50 
//2017.09|21020300E|GL8002|IGLNOASSINGN|ELIMBALANCE_E|66,393,194.36
0