Concatenate values


Hi guys, 


i would like to know  if its possible to concatenate values like this to create a code: 

- i have three line items : Year; Currency; Group 

- the final output i want for my code is : TypeYearCurrencyGroup

i dont want to add a list or create a manual entry for 'Type' because it will always be the same since there is just one value. is there a way to concatenate these items in order to autogenerate a code with a fixed 'type' at the begining followed by the other values ? 



Best Answer

  • Misbah
    Answer ✓



    If TYPE is always going to be same for each list item then hardcode it to that value in the formula bar

    For example

    if it is the list format then List Name. List item

    or else if it is text field then put it in Double Quotes ""




  • @pdekas 


    Create a fourth line item which will accommodate the code and formula will be "TYPE"&Year&Currency&Group

    Depending upon the format of input line item extract out the text from them.




  • You need to limit concatenation as much as possible, see my article Memory Usage From Text Concatenation 

    This can be done by doing as many of the concatenations at the lowest cell counts possible.
    So if you would add "Type" to Year at the Year level (assuming this is a time dimension).
    You might then add that part to the Currency (this is likely to be smaller than Group)
    Then add that part to the final Group part.
    (All assuming you are adding codes of lists, this doesn't apply if they are just values against a single list).

    I would question this code though. A code like this needs to be unique and I'd guess many of these will only vary by Group; Year and Currency could be quite common.
    Is it not possible to dimension your data by Time and Currency - importing in to a module dimensioned by Group, Year and Currency.
    My point is, look for strategies that avoid the need for text and text concatenation where possible, if this means reevaluating the source data then it should be done; the benefits will pay off over time.