Do I need to / how do I concatenate in rule

[ADW]

I have this feeder:

Code: Select all

['Effect'] => DB('Headcount Intermediate', 'Forecast', !Year, 
		           DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Effective AP'), 
		           !Cost Centre, 
		           DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Level'), 
		           !Heacount Position Number, 'TEST') ;

The 'Effective AP' part of the code will return a period 1 - 12 (numerical only), however the dimension it flows into (Month) contains AP 1 - AP 12 (text). How can I append "AP" infront of DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Effective AP')? Month number (1 - 12) is an attribute of Month so does it even need AP putting on the front??

[Catherine]

Hi,

To append "AP" infront of DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Effective AP'), you have to write it this way:

'AP ' | DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Effective AP')

[qml]

Use the pipe (|) to concatenate strings like below (note it would also work without getting the spaces right by using TRIM, but I like to keep things looking clean). If 'Effective AP' is a numeric measure, you also need to convert the value to a string using STR.

Code: Select all

['Effect'] => DB('Headcount Intermediate', 'Forecast', !Year, 
		           'AP ' | TRIM( STR( DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Effective AP'), 2, 0)), 
		           !Cost Centre, 
		           DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Level'), 
		           !Heacount Position Number, 'TEST') ;

If month number is just a numeric attribute of month (an not an alias) then you can't use it to reference the elements in a feeder statement. If it's an alias, you can use them like there were normal element names.

[Duncan P]

It's in the documentation but I remember also finding it difficult to locate. It doesn't appear to be in the "Reference" but I did find it squirreled away at the end of the "Syntax for Formulas"[sic] section of the "Developer Guide" (together with an example which is just plain wrong).

[ADW]

Thanks for you replies. I had the pipe seperator but not the convert to string. I will give that a go.

Cheers.

[ADW]

That works as it should now. Thanks.

I now have a different issue. I need the feeder to feed to all APs equal to or larger than 'Effective AP'.

Code: Select all

['Effect'] => DB('Headcount Intermediate', 'Forecast', !Year, 
		           'AP' | TRIM( STR( DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Effective AP'), 2, 0 )),
		           !Cost Centre, 
		           DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Level'), 
		           !Heacount Position Number, 'TEST') ;

[qml]

I now have a different issue. I need the feeder to feed to all APs equal to or larger than 'Effective AP'.

The easiest way would be to create a set of Year-to-Go consolidations in the AP dimension similar to this (adjust the naming convention to whatever you think best):

• AP 1 YTG
  AP 1
  AP 2
  AP 3
  AP 4
  AP 5
  AP 6
  AP 7
  AP 8
  AP 9
  AP 10
  AP 11
  AP 12

• AP 2 YTG
  AP 2
  AP 3
  AP 4
  AP 5
  AP 6
  AP 7
  AP 8
  AP 9
  AP 10
  AP 11
  AP 12

• AP 3 YTG
  AP 3
  AP 4
  AP 5
  AP 6
  AP 7
  AP 8
  AP 9
  AP 10
  AP 11
  AP 12

etc until "AP 12 YTG".

Then you can just slightly amend your feeder statement to feed the right one of these consolidations (which is shorthand for feeding their children) to get the desired effect.

Code: Select all

['Effect'] => DB('Headcount Intermediate', 'Forecast', !Year, 
                 'AP' | TRIM( STR( DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Effective AP'), 2, 0 )) | ' YTG',
                 !Cost Centre, 
                 DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Level'), 
                 !Heacount Position Number, 'TEST');

[ADW]

Thanks for that. I think we're almost there now!!!

Last issue (hopefully).

In the destination cube the start month is being fed but the months after are not. They appear but do not consolidate. (I have a feeling that didn't make sense so I've added a picture)

Dest cube view.png (9.63 KiB) Viewed 12870 times

[ADW]

It's OK. Done it now.

Had a space missing between AP and the number....Doh!!

[Steve Rowe]

Are you sure that was your issue? As far as I'm aware TM1 has never cared about spaces (or cases) in element names.
Have done a full restart of your environment since you finished writing your rules / feeders? This is the only 100% safe way to tell if you rules are working.
The feeder flags persist in the cube through repeated saves of the rule sheet, this means that when you save a rule sheet you have the feeders of the current rule set and the feeders of all previous rules saved since the last reboot.

This can lead to your code to stop working following a restart.
Cheers

[ADW]

Not sure how things should work but that space makes all the difference. Took the space out and restarted the server, feeder doesn't work as I want it to, put the space back in and everything is fine again!

[Steve Rowe]

Interesting, would you mind posting the different flavours of the rule highlighting where the space is?
Cheers

[ADW]

hmmm...I may need to retract my claim. The feeder rule works for an extra month each time I press save...I don't get it...