Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only
Back to discussions
Expand all | Collapse all

TI Process to add new element to dimension with automated Element Name template

  • 1.  TI Process to add new element to dimension with automated Element Name template

    Posted Fri July 07, 2023 04:10 AM

    Hi guys, im having some question, is it possible for me to can make a TI Process where when user trigger that process, it will be automated adding new element to some dimension with automated element name template?

    example : i have the dimension called 'Product' which consist of 

    PDT001

    PDT002

    And i want to create a TI process where it will be creating a new dimension element with that "PDT002"(Current element) + 1

    so it will be "PDT003" and so on everytime user run that process. Is it possible to do that? your help will be very helpful for me thank you all



    ------------------------------
    calvin winardi
    ------------------------------


  • 2.  RE: TI Process to add new element to dimension with automated Element Name template

    Posted Fri July 07, 2023 08:14 AM

    Yes it is possible in several ways.  The simplest way is to create a MDX based temporary subset sorted in reverse, reference the first element in the subset,  perform a substring of the last x characters, convert to a number, add 1, convert back to string and concatenate with the relevant prefix then insert the element.



    ------------------------------
    John O'Leary
    ------------------------------

    Original Message


  • 3.  RE: TI Process to add new element to dimension with automated Element Name template

    Posted Fri July 07, 2023 09:50 PM

    Hi thanks for the advice i already did that but the main problem is when it comes to the 10, it will be "PDT0010" bot a "PDT010" and so on... it also will be 'PDT00100' and etc



    ------------------------------
    calvin winardi
    ------------------------------

    Original Message


  • 4.  RE: TI Process to add new element to dimension with automated Element Name template

    Posted Sun July 09, 2023 07:58 PM
    This is some recent code I have written, which is based on something someone else had already written so I don't claim originality.  
    # Constants
    #--dimension to update
    cs_DimName = 'Capital Planning Asset';
    cs_AllPlannedAssets = 'All Planned Assets';
    if(DimensionExists(cs_DimName)=0);
        DimensionCreate ( cs_DimName );
    Endif;
    HierarchyElementInsert(cs_DimName, cs_DimName, '', cs_AllPlannedAssets, 'C');
    sSub_Src_PA00 = 'Src.' | s_UniqueName |'_PA00';
    #-- counters
    n_ErrorCount = 0;
    #-- generate a code
    #-- create a subset of all Planned Assets and count the size. 
    #-- the code seq number is subset size + 1
    #-- Create a Subset
    if(HierarchySubsetExists(cs_DimName, cs_DimName, sSub_Src_PA00)=0);
    HierarchySubsetCreate(cs_DimName, cs_DimName, sSub_Src_PA00, 1);
    endif;
    #--capital  members subset
    cs_MDX  = '{TM1FILTERBYPATTERN( {TM1SUBSETALL(['|cs_DimName|'])}, "PA00*")}';
    #-- Set an MDX
    HierarchySubsetMDXSet(cs_DimName, cs_DimName, sSub_Src_PA00,cs_MDX);
    HierarchySubsetMDXSet(cs_DimName, cs_DimName, sSub_Src_PA00,'');
    #compare new number to cn_MaxNumber and make cn_MaxNumber the higher
    #when finished then change  n_PA_Seq_Number_PA00 =cn_MaxNumber + 1;
    n_SubsetSize_PA00 = HierarchySubsetGetSize(cs_DimName, cs_DimName, sSub_Src_PA00);
    nPlannedAsset_Subset_Index = 1;
    cn_MaxNumber = 0;
    cs_PA_Number_As_Str = '';
    cn_PA_Number_As_Numbr = 0;
    while (nPlannedAsset_Subset_Index <= n_SubsetSize_PA00);
        sPASubsetCurrentElementName = HierarchySubsetGetElementName(cs_DimName,cs_DimName ,sSub_Src_PA00, nPlannedAsset_Subset_Index);
        cs_PA_Number_As_Str = subst(sPASubsetCurrentElementName, 5 , long(sPASubsetCurrentElementName) -4 );
        cn_PA_Number_As_Numbr = numbr (cs_PA_Number_As_Str);
        if (cn_PA_Number_As_Numbr > cn_MaxNumber);
          cn_MaxNumber = cn_PA_Number_As_Numbr;
       Endif;
        nPlannedAsset_Subset_Index = nPlannedAsset_Subset_Index + 1;
    end;
    #-- working out a seq number of Planned Asset
    n_PA_Seq_Number_PA00 = cn_MaxNumber + 1;
    s_PA_Seq_Number_PA00 = NumberToString(n_PA_Seq_Number_PA00);
    #-- Planned Capital calculated Seq number
    s_PA_Code_PA00 = 'PA00' | FILL('0', 7 - LONG(s_PA_Seq_Number_PA00))|s_PA_Seq_Number_PA00;
    #-- add a new Planned Asset member
    HierarchyElementInsertDirect( cs_DimName,cs_DimName, '' ,s_PA_Code_PA00, 'N');
    #-- assign to Parent
    HierarchyElementComponentAdd(cs_DimName, cs_DimName,  cs_AllPlannedAssets ,s_PA_Code_PA00, 1);


    ------------------------------
    John O'Leary
    ------------------------------

    Original Message


  • 5.  RE: TI Process to add new element to dimension with automated Element Name template

    Posted Tue August 22, 2023 10:05 PM

    Helloo sir, thank you for the syntax it helps me alot through my process thanks



    ------------------------------
    calvin winardi
    ------------------------------

    Original Message


  • 6.  RE: TI Process to add new element to dimension with automated Element Name template

    Posted Fri July 07, 2023 07:19 PM

    Hello,

    If you would be happy with these elements:

    PDT
    PDT_1
    PDT_2
    PDT_3
    PDT_4
    PDT_5
    PDT_6
    PDT_7
    PDT_8
    PDT_9
    PDT_10
    PDT_11

    Then you can suffice with almost a oneliner:

    vDim = 'Product';
    AttrInsert( vDim, '', 'Desc', 'A' );
    DimensionElementInsertByAlias( vDim, '', 'Desc', 'PDT', 'N' );
    Creating the alias attribute is a one-off exercise and does not be in the code after attribute creation.
    Information on this rather obscure function: 
    https://www.tm1forum.com/viewtopic.php?t=15997
    Thanks to George Tonkin for reminding me of the work we did together in figuring out what this function does.
    If you do want the exact pattern of characters, then you would use an attribute or simple cube to store the number of already created elements.
    Or use DIMSIZ or ELCOMPN, etc. Then increment 1 and create the new element. Should be rather straightforward.
    Or just retrieve the last element in the dimension / last child of a consolidation and work from there.


    ------------------------------
    Wim Gielis
    ------------------------------

    Original Message


  • 7.  RE: TI Process to add new element to dimension with automated Element Name template

    Posted Fri July 07, 2023 09:53 PM

    hi sir, thanks for the advice but the main problem is what i need is an "PDT + 3 Digits of number" so it will be PDT001, PDT010, PDT100 Actually i tried ti create a number attribute to handle that but it seems that the attribute stored as 1 rather than 001



    ------------------------------
    calvin winardi
    ------------------------------

    Original Message


  • 8.  RE: TI Process to add new element to dimension with automated Element Name template

    Posted Fri July 07, 2023 10:08 PM

    Then you will need to use your own functions like the below with a String attribute, not numeric attribute:

    DimensionElementInsert

    AttrS

    StringToNumber

    NumberToStringEx (with '000' as the 2nd argument)

    AttrPutS

    Elcomp and ElcompN (or Dimsiz and Dimnm)



    ------------------------------
    Wim Gielis
    ------------------------------

    Original Message


  • 9.  RE: TI Process to add new element to dimension with automated Element Name template

    Posted Mon July 10, 2023 03:21 AM

    why don't use If function?

    example:

    (getcurrentelement

    example: currentEl =  'PDT002'

    elno = NUMBR(subst(currentEl,4,3))

    elno = elno+1;

    NewElement = 'PDT' |  if(elno<10,'00',
    if (elno<100,'0','') |  trim( str(elno,3,0));
    );



    ------------------------------
    Veronika Gultom
    ------------------------------

    Original Message


  • 10.  RE: TI Process to add new element to dimension with automated Element Name template

    Posted Mon July 10, 2023 11:10 AM

    Another option is to convert the last three digits to a number, add 1000, increment the number, convert back to a string and then do a substring on the last three digits.

    sEle = 'PDT002';
    sPre = SubSt( sEle, 1, 3);
    sBaseNum = SubSt( sEle, 4, 3);
    nBaseNum = 1000 + Numbr( sBaseNum);
    nNewNum = nBaseNum + 1;
    sNewNum = SubSt( NumberToString( nNewNum), 2, 3);
    sNewEle = sPre | sNewNum;



    ------------------------------
    Scott Brown
    ------------------------------

    Original Message


  • 11.  RE: TI Process to add new element to dimension with automated Element Name template

    Posted Mon July 10, 2023 11:18 AM

    sEle = 'PDT002';


    sPre = SubSt( sEle, 1, 3);
    sNewNum = NumberToStringEx( StringToNumber( SubSt( sEle, 4, 3 )) + 1, '000', '', '' );
    sNewEle = sPre | sNewNum;



    ------------------------------
    Wim Gielis
    ------------------------------

    Original Message


  • 12.  RE: TI Process to add new element to dimension with automated Element Name template

    Posted Mon July 10, 2023 01:16 PM

    perfect!

    but need to replace value of sEle:

    sEle = sNewEle;



    ------------------------------
    Veronika Gultom
    ------------------------------

    Original Message


Related Content