Attributes changes

[Mithun.Mistry1103]

Hello friends

Would it be possible to advise the best possible way to change parts of a string in an attribute please?
The attribute has strings like 'Jan 14' per each element in a large dimension but also strings like 'Jan 13' in the same attribute(text). Each year will be a +1 so 'Jan 14' will be 'Jan 15' after a year and currently we have to manually change those numbers.

I have tried doing it the cube way, which is to create few attributes and then concat but thats long winded. I will get so many rules which will become messy.
I am looking at TI's so it can look for the number 15 and replace so it becomes 16. But I have had no luck when I have searched for ideas or functions.

Any ideas or help would be appreciated.



Regards
Mithun

[BariAbdul]

You can use SUBST and Trim to get desired string out of Date string, For updating attributes through TI you can use AttrPutN/AttrPutS.

Code: Select all

AttrPutN(Value, DimName, ElName, AttrName);

But remember when you are updating attributes ,update in Data tab.
Thanks

[jim wood]

You could also do this via a rule. Use Subst(today(),1,2) convert to a number, add 1 then convert back to string. This way you'll never need to run a process. It will automatically change when you roll in to a new year. You can flex this based on a original year say.

[rmackenzie]

Code: Select all

sOriginalString = 'jan 14, jan 15, jan 16';
sToken = 'jan 15';
sNewToken = 'santa claus';
nTokenStart = SCAN ( sToken, sOriginalString );

IF ( nTokenStart > 0 );
  sNewString = SUBST ( sOriginalString, 1, nTokenStart - 1 )
    | sNewToken 
    | SUBST ( sOriginalString, nTokenStart + LONG ( sToken ), LONG ( sOriginalString ) - nTokenStart - LONG ( sToken ) +1 );
ELSE;
  sNewString = sOriginalString;
ENDIF;

[Mithun.Mistry1103]

Hello friends,

Thank you for all the suggestions.

@BariAbdul

Code: Select all

AttrPutN(Value, DimName, ElName, AttrName);

This would be ok if I had one element but what would I need to do if there are approx 100 odd elements? The main point is although its +1 to every year, some will be, previous year, current year, some will be next year which is 2016.
I am trying to avoid the stack of code we would need to write.

Is there a possibility that we can create a loop that does :

I am an attribute: (NAME) and I want to add 1 to all the elements that have 2 number characters, whether it is 14, 15, 16, bearing in mind, the type will be text as those two numbers characters will also include 3 character string (Month) e.g. JAN 14

I do think rmackenzie's solution is interesting, although, again, I would assume it can only be done for specified strings and like I mentioned, I have approx 100 odd elements so I would need to specify all elements to get it to update.

I have already tried creating 3 attributes with parts of the string which then concat at the final step but its too long winded and we need so many rules just to concat. Theres too many disadvantages if I went with this method.

Thank you for the help and time

[stephen waters]

If it is a one-off or once a year and you are just doing it for 100 elements in a single dimension why not use DBSA in a sporeadsheet ? Very easy to do string manipulation in Excel formulae

[declanr]

What is wrong with using code like Robin provided?

The whole point is that the code would be inside another while loop that cycles through all the elements in question and performs the scan replace for that element.

[Mithun.Mistry1103]

Hello Declanr

Theres nothing wrong with the code Robin provided, as I do find it interesting, the only issue is how it fits the bill for me.

If we look at the code, from what I understand, the originalstring will need to contain all the elements that will be searched

Code: Select all

sOriginalString = 'jan 14, jan 15, jan 16';

For me it would be -

Code: Select all

 sOriginalString = ' Jan 13, Jan 14 ,Jan 15, Feb 13, Feb 14, Feb 15,  Mar 13, Mar 14, Mar 15, Apr 13, Apr 14, Apr 15, May 13, May 14, May 15, Jun 13, Jun 14, Jun 15, Jul 13, Jul 14, Jul 15, Aug 13, Aug 14, Aug 15, Sep 13, Sep 14, Sep 15, Oct 13, Oct 14, Oct 15, Nov 13, Nov 14, Nov 15, Dec 13, Dec 14, Dec14';

Then we come to sToken

Code: Select all

'jan 15';

From what I understand from this code is sToken will look for the entire string (Jan 15) which means I would have to do it numerous times because there are about 72 different types of strings 12 months with 13, 12 months with 14 and 12 months with 15 and then there are other strings such as Jan 15 YTD etc.

The last parameter is:

Code: Select all

 sNewToken = 'santa claus';

From this, I believe this is the replacement string. Again, It would require me to do it 72 times if my above understanding is correct.

Unless we can create it in a way that says look for 5 in the attribute " Example" and add 1 or we say add 1 to every 5th character in that entire attribute

Thank you soo much for all the time and advise

[rmackenzie]

I am trying to avoid the stack of code we would need to write.

True, a TI based solution starts to get unwieldy if you need to need to loop over various input strings and do multiple find-and-replace on the original string. You could look at the Java integration if your TM1 version is new enough as it will enable you to leverage a regular expression engine (which is what you need). Alternatively you can access regex engines in VBScript and Powershell both of which can be called from ExecuteCommand. There's plenty of follow-up material on the internet - check out this thread on Stack Overflow for example.

[Mithun.Mistry1103]

Hello

Thank you for your reply

TI based solution starts to get unwieldy if you need to need to loop over various input strings and do multiple find-and-replace on the original string

I have just looked at the solutions you suggsted and spoke to one of my colleagues - He has mentioned using the code you have given but to use cellputS instead of specifying every string .... now the issues I think would occur is the amount of elements we have to specify (the dimensional elements) we will have loads, and the fact that it is not talking about attributes might still be indicating that it might not work

What would you suggest in terms of using cellputS? The other methods you mentioned will be something I will look at going into the year but at the moment, as its an urgent thing for us, we are likely to try find a ti solution.

Thank you for the time sir

[Mithun.Mistry1103]

Hello

Any suggestion in a loop that changes the 6th character in all the 0 level node in an attribute...so we can say take 6th character, add one?

If we replace the whole strings, we will go mental naming all the strings etc...

Any ideas?
Thank you

[declanr]

Code: Select all

iCount=1;
iMax=DimSiz (sDim);
While (iCount <=iMax);
      sElement=Dimnm (sDim, iCount);
      sOldAttr=AttrS (sDim, sElement,'attr');
      sPre=SubSt (sOldAttr,1,5);
      sPost=SubSt (sOldAttr, 7, long (sOldAttr) - 6);
      sChange=NumberToString (StringToNumber(SubSt (sOldAttr,6,1))+1);
      AttrPutS (sPre|sChange|sPost, sDim, sElement,'Attr');
      iCount=iCount+1;
End;

Written on my phone so i apologise for typos. There are many ways of doing this, i have tried to break down the components so you can see how it works to the end result.

I think the ehole topic may be overcomplicating the matter as although other tools etc do make find and replace easier by taking it completely out of the users hands, doing so in TI really isn't that difficult.
If you are still struggling perhaps providing and exerpt of your dimension and the attributes you need to update would get the topic closed quickly.

[Mithun.Mistry1103]

Hello declanr

Thank you for that code

I believe it will work as I have tried it and the errors that are occuring are refering to the strings in the attribute, as some of the strings dont have numbers as they are titles. It says it cannot convert strings to real numbers which makes sense. And the other cause is the uniqueness of the data...so 15 would need to change to 16 first before 14 can change to 15 ..
The image is a example of the attribute in the dimension that we have ..

Thank you for your time and effort and sorry for the inconvenience

[Mithun.Mistry1103]

Thanks for all your help...I know have a solution for this

thanks again

[Carl Lonsdale]

If this is a one shot deal, sometimes if I am lazy I use Excel to do it for me:
Since you can Type out 3 Letter Months in Excel and click and drag to complete, same for Numbers, etc.

Then copy and paste the values into the corresponding control cube.

which is }ElementAttributes_Time
(Assuming your dimension is named Time)
This is a String Cube in which you can manually edit the string Values.

This allows you to pick a specific set or subset of elements to edit via the subset editor.
Then you can copy and paste the elements from Excel. TM1 Matches the Excel array , so as long as you paste in a list of the elements you want to change into the Subset for Time, and crop the Attributes in the Subset for Attributes (at the top), you can paste in as many changes as needed.

If this is not a one shot, I would almost certainly write the TI.