Skip to Content
avatar image
Former Member

Conditional Records based on dimension property

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Nov 30, 2017 at 07:37 AM

    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
    Add comment
    10|10000 characters needed characters exceeded

    • 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