Page 1 of 1
Do I need to / how do I concatenate in rule
Posted: Tue Nov 22, 2011 10:38 am
by 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??
Re: Do I need to / how do I concatenate in rule
Posted: Tue Nov 22, 2011 11:24 am
by 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')
Re: Do I need to / how do I concatenate in rule
Posted: Tue Nov 22, 2011 11:32 am
by 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.
Re: Do I need to / how do I concatenate in rule
Posted: Tue Nov 22, 2011 11:45 am
by 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).
Re: Do I need to / how do I concatenate in rule
Posted: Tue Nov 22, 2011 12:20 pm
by ADW
Thanks for you replies. I had the pipe seperator but not the convert to string. I will give that a go.
Cheers.
Re: Do I need to / how do I concatenate in rule
Posted: Tue Nov 22, 2011 12:44 pm
by 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') ;
Re: Do I need to / how do I concatenate in rule
Posted: Tue Nov 22, 2011 1:20 pm
by qml
ADW wrote: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');
Re: Do I need to / how do I concatenate in rule
Posted: Wed Nov 23, 2011 3:23 pm
by 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 12992 times
Re: Do I need to / how do I concatenate in rule
Posted: Wed Nov 23, 2011 3:46 pm
by ADW
It's OK. Done it now.
Had a space missing between AP and the number....Doh!!
Re: Do I need to / how do I concatenate in rule
Posted: Thu Nov 24, 2011 1:16 pm
by 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
Re: Do I need to / how do I concatenate in rule
Posted: Fri Nov 25, 2011 8:47 am
by 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!
Re: Do I need to / how do I concatenate in rule
Posted: Mon Nov 28, 2011 12:16 pm
by Steve Rowe
Interesting, would you mind posting the different flavours of the rule highlighting where the space is?
Cheers
Re: Do I need to / how do I concatenate in rule
Posted: Tue Nov 29, 2011 11:09 am
by 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...