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 ?
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.2