Script access to cells in 3 or more dimensions

5.76K viewsScripting
0

I have been trying to get my head around the pipe syntax and cannot fathom how to access cells and ranges of cells in a script, particularly when more than two dimensions are involved.

One example of what I want to do is:-

I have a matrix P&L with categories:
Quarters: Qtr1,Qtr2,Qtr3,Qtr4
Items: Sales, COGS and Margin
Region: North, South

I want to be able to set Quarters Qtr2,Qtr3,Qtr4 to the Qtr1 value across all categories using a script.

Syntactically, it seemed to me I wanted to say:

[code:2nizbame]|’P&L’::Qtr2..Qtr4|.values=|’P&L’::Qtr1|.value;[/code:2nizbame]

But this simply sets all quarters to the North/Sales/Qtr1 value rather than the ‘relevant’ Qtr1 value.

The expression:

[code:2nizbame]|’P&L’::South:COGS:Qtr2..Qtr4|.values=|’P&L’::South:COGS:Qtr1|.value;[/code:2nizbame]

Works correctly, but requires repeating for each Regions/Item and necessitates change when we add new regions.

I then tried using the [b:2nizbame].each[/b:2nizbame] function to handle each region separately but cannot get around the syntax needed to access the relevant items (Sales and COGS) to do this. What I had in mind was something like:-

[code:2nizbame]|’P&LTable’::Region|.children.each{region->
region:Sales:Qtr2..Qtr4|.values=region:Sales:Qtr1|.value; // wrong ... syntax error
region:COGS:Qtr2..Qtr4|.values=region:COGS:Qtr1|.value; // wrong ... syntax error
}
[/code:2nizbame]

Is this the reasonable approach, and if so, how should these expressions read?

Further, ideally we would want to do another .each within the region.each to cover the item selection, e.g. region|Sales..COGS|.children.each{item-> etc. }

As a footnote, it would be very helpful if the script example Selections.model was extended to use a three or more dimensional matrix.

0

Ben, grebe,

I can confirm that the code provided by you, Ben, is working and the better solution than mine because it is more generic.

[code:3ujqzzy6]
|’P&L’::Quarters|.children.each
{
it.values = |’P&L’::Qtr1|.values
}
[/code:3ujqzzy6]

Dominik

You are viewing 1 out of 6 answers, click here to view all answers.