Script access to cells in 3 or more dimensions

5.58K 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

Hi grebe,

I am not sure but this script could help you to solve your requirements or to show you a possible direction at least:

[code:1h2rikwg]
for (def int i = 1; i<= 3; i++)
{
|’P&L’::Quarters|.children[i].values = |’P&L’::Qtr1|.values
}
[/code:1h2rikwg]

You can use the attached model to test this code snippet.

Alle the best,
Dominik

0

Hi grebe,

Dom beat me to an answer, but I already started writing mine so I might as well finish it.

The first hint I would offer is that in addition to getting the [font=””Courier New””]value[/font] property of an object, you can also get a [font=””Courier New””]value[b:1pk5psny][u:1pk5psny]s[/u:1pk5psny][/b:1pk5psny][/font] property for objects that represent ranges. The singular version gives you the first value in the range, whereas the plural gives you a list of all values. When you are looking to copy the contents of a range (such as the contents of the Qtr1 item) using an assignment statement, you’ll have better luck if you use [font=””Courier New””]values[/font] on the right hand side of the assignment as opposed to [font=””Courier New””]value[/font].

Having noted that, your third attempt above is actually pretty close to the mark. If we use the [font=””Courier New””]values[/font] property, you can do the following which (I think) does what you want:

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

The reason for the syntax error when you try to write the closure the way you indicated is that this expression:

[code:1pk5psny]
region:Sales:Qtr2..Qtr4|.values
[/code:1pk5psny]

Mixes Quantrix pipe syntax with generic Groovy code: [font=””Courier New””]region[/font] is just a normal variable in Groovy. This won’t work because Groovy doesn’t understand Quantrix ranges. The Groovy compiler doesn’t realize that it’s inside of Quantrix and that we have pipe selections, so it doesn’t know that [font=””Courier New””]Sales:Qtr2..Qtr4[/font] is something that has meaning relative to the value of [font=””Courier New””]region[/font]. Instead, it is trying to figure out what the “”:”” operator does in Groovy and what it means when its operands are the variable “”region”” and the (undeclared) variable “”Sales””. The answer is that, to Groovy, there is no such operator and this expression doesn’t make sense. When you enclose a complete Quantrix selection in pipes, like [font=””Courier New””]|’P&L’::Region:Sales:Qtr2..Qtr4|[/font], we do some work under the hood to chew that up and render it into something else that Groovy understands, before it hits the Groovy compiler.

Hope this helps. Let us know if you have more questions.

-Ben

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

0

Many thanks both Ben and Dom, this has taken my understanding on a long way; the code applies to all Regions and Items successfully.

I then tried to extend this principle to apply this to only a single item (e.g.Sales) in the Item category, across all other categories.

I had hoped I might extend the pipe to something like:-

[code:1efq3slo]|’P&L’::Sales:Quarters|.children.each{ ...[/code:1efq3slo]

but it seems as soon as you mention a specific item the pipe does not produce a ‘group’ (i.e. you cannot use an [i:1efq3slo]each[/i:1efq3slo]).

So, I have tried various permutations of

[code:1efq3slo]|’P&L’::Sales:Quarters|.values = |’P&L’::Sales:Qtr1|.values;[/code:1efq3slo]

resulting in some strange placements of the values which seems to be a result of the order of evaluation.

Any ideas on the syntax needed to achieve this correctly?

I wondered if there was a way to put a construct around the original pipe.each to limit the range over which it was evaluated?

best
Nigel

0

Hi Nigel,

I think this can be accomplished by working in slightly broader strokes with the selections. Does the following do what you want? Keep in mind that it won’t affect the values in the “”Margin”” item because they are calculated by a formula.

[code:27nf2ye1]
|’P&L’::Items|.children.each
{
it.values = |’P&L’::Sales|.values
}
[/code:27nf2ye1]

Regards,
-Ben

0

Hi Ben

thanks for this, but what I was trying to achieve was, as a formulae,

[code:1n2nrjo3]In Sales,Qtr2 .. Qtr4 = Qtr1[/code:1n2nrjo3]

Doing it in the script, allows the user to change the Qtr2..Qtr4 values, or reset all to the Qtr1 values.

I’ve been round a few circles assuming it was possible to ‘nest’ the .each{} construct, but it seems not.

Anyhow, many thanks for your help
regards
Nigel

Latest Questions

Qloud Losing Formatting 4 Answers | 0 Votes
Meditation on timelines 3 Answers | 0 Votes