cancel
Showing results for 
Search instead for 
Did you mean: 

Sum bbdd=0

monica_marquez2
Participant
0 Kudos

Hi all!!  We have a issue because when we try to add a dataset

We have to do the following operation DF000+ DF001, but we have a problem when there are no data DF001.

Thus, for exemple:

We try to calculate this:  T_FIN = DF000+DF001- (INPUT + + M_1),   (so T_FIN= A-B view picture)

We do not know what happens, because we use this formula in another country and working properly, the only idea we have is that this does not work well when any DF001, m_1, m_2 there are no data.

Any idea?

// A) Agrupación de DF000 |totales+ DF001 |totales --> CDUMMY

*XDIM_MEMBERSET ACCOUNTL= BAS(R000)

*XDIM_MEMBERSET AIRCRAFT= A99999

*XDIM_MEMBERSET BASE= <ALL>

*XDIM_MEMBERSET CATEGORY= ACTUAL

*XDIM_MEMBERSET CECO= CE99999

*XDIM_MEMBERSET CGROUPS=LC

*XDIM_MEMBERSET CONTRACT= NO_CONTRACT

*XDIM_MEMBERSET DATASRC= DF000,DF001

*XDIM_MEMBERSET INTCOL= <ALL>

*XDIM_MEMBERSET LEGALENTITY=%LEGALENTITY_SET%

*XDIM_MEMBERSET TIME=%TIME_SET%

*WHEN ACCOUNTL

*IS BAS(R000)

     *WHEN AIRCRAFT

     *IS *

          *WHEN CECO

          *IS *

                *WHEN CONTRACT

                *IS *

                     *WHEN DATASRC

                     *IS "DF000","DF001"

                            *REC(FACTOR=1, DATASRC="CDUMMY",AIRCRAFT="A99999", CECO="CE99999", CONTRACT="NO_CONTRACT",BASE="BDJMMY",INTCOL="I_NONE")

                                                   *ENDWHEN

               *ENDWHEN    

          *ENDWHEN

       *ENDWHEN

*ENDWHEN

*COMMIT

// B) Agrupación de INPUT|totales + BAS(T_MAN)|totales --> DDUMMY

*XDIM_MEMBERSET ACCOUNTL= BAS(R000)

*XDIM_MEMBERSET AIRCRAFT= BAS(A_TOTAL)

*XDIM_MEMBERSET BASE= BAS(L00000)

*XDIM_MEMBERSET CATEGORY= ACTUAL

*XDIM_MEMBERSET CECO= BAS(CE_TOTAL)

*XDIM_MEMBERSET CGROUPS=LC

*XDIM_MEMBERSET CONTRACT= BAS(C_TOTAL)

*XDIM_MEMBERSET DATASRC= INPUT,M_1,M_2,M_3

*XDIM_MEMBERSET INTCOL= BAS(ALL_INTERCO)

*XDIM_MEMBERSET LEGALENTITY=%LEGALENTITY_SET%

*XDIM_MEMBERSET TIME=%TIME_SET%

*WHEN ACCOUNTL

*IS *

     *WHEN AIRCRAFT

     *IS *

*WHEN DATASRC

*IS "INPUT","M_1","M_2","M_3"

     *REC(FACTOR= 1, AIRCRAFT="A99999", BASE="BDJMMY", CECO="CE99999", CONTRACT="NO_CONTRACT", DATASRC="DDUMMY", INTCOL="I_NONE")              

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

// C) Cálculo T_FIN CDUMMY-DDUMMY --> T_FIN

*XDIM_MEMBERSET ACCOUNTL= BAS(R000)

*XDIM_MEMBERSET AIRCRAFT= A99999

*XDIM_MEMBERSET BASE= BDJMMY

*XDIM_MEMBERSET CATEGORY= ACTUAL

*XDIM_MEMBERSET CECO= CE99999

*XDIM_MEMBERSET CGROUPS=LC

*XDIM_MEMBERSET CONTRACT= NO_CONTRACT

*XDIM_MEMBERSET DATASRC= CDUMMY,DDUMMY

*XDIM_MEMBERSET INTCOL= I_NONE

*XDIM_MEMBERSET LEGALENTITY=%LEGALENTITY_SET%

*XDIM_MEMBERSET TIME=%TIME_SET%

*WHEN DATASRC

*IS "CDUMMY"

     *REC(EXPRESSION= %VALUE%-([DATASRC].[DDUMMY],[BASE].[BDJMMY]), BASE="L99999", DATASRC="T_FIN")              

*ENDWHEN

*COMMIT

Thanx in advance!

Monika

View Entire Topic
monica_marquez2
Participant
0 Kudos

oops! This is the code:

Thanx!!

// A) Agrupación de DF000 |totales+ DF001 |totales --> CDUMMY

*XDIM_MEMBERSET ACCOUNTL= BAS(R000)

*XDIM_MEMBERSET AIRCRAFT= A99999

*XDIM_MEMBERSET BASE= <ALL>

*XDIM_MEMBERSET CATEGORY= ACTUAL

*XDIM_MEMBERSET CECO= CE99999

*XDIM_MEMBERSET CGROUPS=LC

*XDIM_MEMBERSET CONTRACT= NO_CONTRACT

*XDIM_MEMBERSET DATASRC= DF000,DF001

*XDIM_MEMBERSET INTCOL= <ALL>

*XDIM_MEMBERSET LEGALENTITY=%LEGALENTITY_SET%

*XDIM_MEMBERSET TIME=%TIME_SET%

*WHEN ACCOUNTL

*IS BAS(R000)

     *WHEN AIRCRAFT

     *IS *

          *WHEN CECO

          *IS *

                *WHEN CONTRACT

                *IS *

                     *WHEN DATASRC

                     *IS "DF000","DF001"

                            *REC(FACTOR=1, DATASRC="CDUMMY",AIRCRAFT="A99999", CECO="CE99999", CONTRACT="NO_CONTRACT",BASE="BDJMMY",INTCOL="I_NONE")

                                                                            *ENDWHEN

                    *ENDWHEN    

               *ENDWHEN

       *ENDWHEN

*ENDWHEN

*COMMIT

// B) Agrupación de INPUT|totales + BAS(T_MAN)|totales --> DDUMMY

*XDIM_MEMBERSET ACCOUNTL= BAS(R000)

*XDIM_MEMBERSET AIRCRAFT= BAS(A_TOTAL)

*XDIM_MEMBERSET BASE= BAS(L00000)

*XDIM_MEMBERSET CATEGORY= ACTUAL

*XDIM_MEMBERSET CECO= BAS(CE_TOTAL)

*XDIM_MEMBERSET CGROUPS=LC

*XDIM_MEMBERSET CONTRACT= BAS(C_TOTAL)

*XDIM_MEMBERSET DATASRC= INPUT,M_1,M_2,M_3

*XDIM_MEMBERSET INTCOL= BAS(ALL_INTERCO)

*XDIM_MEMBERSET LEGALENTITY=%LEGALENTITY_SET%

*XDIM_MEMBERSET TIME=%TIME_SET%

*WHEN ACCOUNTL

*IS *

     *WHEN AIRCRAFT

     *IS *

*WHEN DATASRC

*IS "INPUT","M_1","M_2","M_3"

     *REC(FACTOR= 1, AIRCRAFT="A99999", BASE="BDJMMY", CECO="CE99999", CONTRACT="NO_CONTRACT", DATASRC="DDUMMY", INTCOL="I_NONE")              

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

// C) Cálculo T_FIN CDUMMY-DDUMMY --> T_FIN

*XDIM_MEMBERSET ACCOUNTL= BAS(R000)

*XDIM_MEMBERSET AIRCRAFT= A99999

*XDIM_MEMBERSET BASE= BDJMMY

*XDIM_MEMBERSET CATEGORY= ACTUAL

*XDIM_MEMBERSET CECO= CE99999

*XDIM_MEMBERSET CGROUPS=LC

*XDIM_MEMBERSET CONTRACT= NO_CONTRACT

*XDIM_MEMBERSET DATASRC= CDUMMY,DDUMMY

*XDIM_MEMBERSET INTCOL= I_NONE

*XDIM_MEMBERSET LEGALENTITY=%LEGALENTITY_SET%

*XDIM_MEMBERSET TIME=%TIME_SET%

*WHEN DATASRC

*IS "CDUMMY"

     *REC(EXPRESSION= %VALUE%-([DATASRC].[DDUMMY],[BASE].[BDJMMY]), BASE="L99999", DATASRC="T_FIN")              

*ENDWHEN

*COMMIT

former_member186338
Active Contributor
0 Kudos

Hi Monica,

It's very hard to understand the logic behind this script especially with many temporary variables. I have simplified the script based on the following rules:

1. If you scope dimensions before WHEN/ENDWHEN loop you need to scope only required records.

2. In WHEN/ENDWHEN use only one *IS * condition if you have only one *REC

3. In *REC don't use target dimension override if there is no override at all

4. Never use *COMMIT - useless with WHEN/ENDWHEN, but reset the scope (without COMMIT we can remove unnecessary *XDIM's)

// A) Agrupación de DF000 |totales+ DF001 |totales --> CDUMMY

*XDIM_MEMBERSET ACCOUNTL= BAS(R000)

*XDIM_MEMBERSET AIRCRAFT= A99999

*XDIM_MEMBERSET BASE= <ALL>

*XDIM_MEMBERSET CATEGORY= ACTUAL

*XDIM_MEMBERSET CECO= CE99999

*XDIM_MEMBERSET CGROUPS=LC

*XDIM_MEMBERSET CONTRACT= NO_CONTRACT

*XDIM_MEMBERSET DATASRC= DF000,DF001

*XDIM_MEMBERSET INTCOL= <ALL>

*XDIM_MEMBERSET LEGALENTITY=%LEGALENTITY_SET%

*XDIM_MEMBERSET TIME=%TIME_SET%

*WHEN ACCOUNTL

*IS * // Already scoped to BAS(R000)

   *REC(FACTOR=1, DATASRC="CDUMMY",BASE="BDJMMY",INTCOL="I_NONE")

*ENDWHEN

// B) Agrupación de INPUT|totales + BAS(T_MAN)|totales --> DDUMMY

*XDIM_MEMBERSET AIRCRAFT= BAS(A_TOTAL)

*XDIM_MEMBERSET BASE= BAS(L00000)

*XDIM_MEMBERSET CECO= BAS(CE_TOTAL)

*XDIM_MEMBERSET CONTRACT= BAS(C_TOTAL)

*XDIM_MEMBERSET DATASRC= INPUT,M_1,M_2,M_3

*XDIM_MEMBERSET INTCOL= BAS(ALL_INTERCO)

*WHEN ACCOUNTL

*IS *

     *REC(FACTOR= 1, AIRCRAFT="A99999", BASE="BDJMMY", CECO="CE99999", CONTRACT="NO_CONTRACT", DATASRC="DDUMMY", INTCOL="I_NONE")             

*ENDWHEN

// C) Cálculo T_FIN CDUMMY-DDUMMY --> T_FIN

*XDIM_MEMBERSET AIRCRAFT= A99999

*XDIM_MEMBERSET BASE= BDJMMY

*XDIM_MEMBERSET CECO= CE99999

*XDIM_MEMBERSET CONTRACT= NO_CONTRACT

*XDIM_MEMBERSET DATASRC= CDUMMY //,DDUMMY - not used in the loop

*XDIM_MEMBERSET INTCOL= I_NONE

*WHEN ACCOUNTL

*IS *

     *REC(EXPRESSION= %VALUE%-[DATASRC].[DDUMMY], BASE="L99999", DATASRC="T_FIN")             

  //*REC(EXPRESSION= %VALUE%-([DATASRC].[DDUMMY],[BASE].[BDJMMY]), BASE="L99999", DATASRC="T_FIN")         

*ENDWHEN

Recommendations: Rewrite the code without temporary variables, using one formula.

Result=Function(Arg1,Arg2,..,Argn), where n - number of arguments.

You have to repeat calculations n times with WHEN/ENDWHEN loop for members of each argument.

B.R. Vadim

Message was edited by: Vadim Kalinin Additional correction *REC(EXPRESSION= %VALUE%-[DATASRC].[DDUMMY], BASE="L99999", DATASRC="T_FIN") - tuple expression is not required here!

former_member186338
Active Contributor
0 Kudos

The formula without temporary vars will be something like:

*REC(EXPRESSION=[DF000]+[DF001]-[INPUT]-[M_1]-[M_2]-[M_3],...)

We have 6 arguments - 6 WHEN/ENDWHEN loops maximum

Vadim

monica_marquez2
Participant
0 Kudos

Hi Valdim,   we have many members for repeat WHEN/ENDWHEN for each argument  😞

former_member186338
Active Contributor
0 Kudos

Hi Monica,

With your existing code you will always have issues with some missing data!

I have a lot of questions about your code and can't provide the working script... Please, try to rethink the idea!

Questions like:

*XDIM_MEMBERSET AIRCRAFT= A99999

*XDIM_MEMBERSET AIRCRAFT= BAS(A_TOTAL) // A99999 is in A_TOTAL?

*XDIM_MEMBERSET BASE= <ALL>

*XDIM_MEMBERSET BASE= BAS(L00000) //?

etc...

B.R. Vadim