Notifications
Clear all

mapping setup based on criteria and aggregation


Posts: 3
Customer
Topic starter
Active Member
Joined: 8 months ago

Hi, i was needing help with setting up some mapping based on criteria and then using that mapping to carry out some addition/summing between these items. Below is what i am trying to achieve.

I have 2 indexes - "All" & "Months" and a variable - "Current_Vals" (indexed by "All" and "Months") like so:

Index: All Index: Months   Var: Current_Vals      
        1-Jan-22 1-Feb-22 1-Mar-22
Prod_A 1-Jan-22   Prod_A 0 1 1
Prod_A_Overseas 1-Feb-22   Prod_A_Overseas 0 2 2
Prod_B 1-Mar-22   Prod_B 1 2 3
Prod_B_Overseas     Prod_B_Overseas 0 0 0
Prod_C     Prod_C 6 8 10
Prod_C_Overseas     Prod_C_Overseas 2 1 0
Prod_D     Prod_D 5 5 5
Prod_D_Overseas     Prod_D_Overseas 2 2 0

With the mapping, i want to carry out a summation on the "Current_Vals" like so:

  • Prod_A = Prod_A + Prod_A_Overseas
  • Prod_B = Prod_B + Prod_B_Overseas
  • etc

but not this:

  • Prod_A_Overseas = Prod_A + Prod_A_Overseas
  • Prod_B_Overseas = Prod_B + Prod_B_Overseas

So the totalling results like so (interim step):

Var: Interim_Step      
  1-Jan-22 1-Feb-22 1-Mar-22
Prod_A 0 3 3
Prod_A_Overseas 0 2 2
Prod_B 1 2 3
Prod_B_Overseas 0 0 0
Prod_C 8 9 10
Prod_C_Overseas 2 1 0
Prod_D 7 7 5
Prod_D_Overseas 2 2 0

but my final output is this:

Var: Final Output      
  1-Jan-22 1-Feb-22 1-Mar-22
Prod_A 0 3 3
Prod_B 1 2 3
Prod_C 8 9 10
Prod_D 7 7 5

The indexing here can/is different (but a subset of "All").

Any help is greatly appreciated. Thanks

 

 

1 Reply
Posts: 6
Customer
Eminent Member
Joined: 12 months ago

This looks like you have three dimensions, i.e., 

 

Index Product := ['Prod_A', 'Prod_B', 'Prod_C', 'Prod_d']

Index IsOverseas := ['Not Overseas', 'Overseas']

Index Month := 1-Jan-2022 .. 1-Mar-2022

If you organize your data that way, then you can do 

Sum( Current_Vals, IsOverseas )

for your Final_output corresponding to

  • Prod_A = Prod_A + Prod_A_Overseas
  • Prod_B = Prod_B + Prod_B_Overseas
  • etc

 

Reply
Share:
Scroll to Top