I'm trying to concatenate the variables that make up address. With the syntax I'm using, the variables appear in a row separated by space as shown in the code below. However, I want them to appear one below other (like an address would generally appear). Could you please guide me on how to do this?
I'm trying to concatenate the variables that make up address. With the syntax I'm using, the variables appear in a row separated by space as shown in the code below. However, I want them to appear one below other (like an address would generally appear). Could you please guide me on how to do this?
I looked up for carriage return ASCII code (015) and found it to be SI. You mean I should add SI after every variable? Please correct me. Thanks again.
ViRa wrote:Sorry, Carriage Return code is CR. But could you please guide me on how to add it after each variable?
Tomok actually gave you the Octal rather than decimal code for a CR; it's 10 in decimal. You can add that by using the CHAR() rules function. Your code would therefore become:
I tried the code but the address still appears to be in a single row when viewed in Cube or sliced to Excel. Is it how it would appear in Cube view or Excel? Please advise.
10 is for line feed and 13 is carriage return. Should I use a combination of both in order for each variable to appear one below other? Thanks for your time.
I tried the code but the address still appears to be in a single row when viewed in Cube or sliced to Excel. Is it how it would appear in Cube view or Excel? Please advise.
10 is for line feed and 13 is carriage return. Should I use a combination of both in order for each variable to appear one below other? Thanks for your time.
I do not understand. Surely the effort to type out your post is already the equivalent of just trying this out for yourself. Just do it already.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
I had asked guidance to concatenate multiple variables with each variable appearing one below other in order to make an address in TI formula window. As per Tom and Alan's suggestion, I have to insert the Carriage Return ASCII Code along with the CHAR function. But when I used the code that Alan suggested, the variables were appearing in a single row when viewed in Cube or sliced to Excel (as it would if I had concatenated using a space instead of Carriage Return ASCII Code). I googled and found that code 10 is for Line Feed and 13 is for Carriage Return. So I want to know now if I have to use a combination of both these code in order for the variables to appear one below other.
Eg, Currently the variables appear as below -
13th South Street New York NY 1001
But my requirement is as below -
13th South Street
New York
NY 1001
Hope I was able to explain myself clearly this time.
From memory CHAR(13) works when displayed through a slice or snapshot but not in active forms.
I can't recall ever looking at it directly in a cube viewer but would be very surprised if that ever worked.
But to elaborate on Lotsa's point, you had already provided the basis for what you wanted to try, he was suggesting it would have been quicker for you to write that code, run the TI and check the results than it would have been to post here and wait for a response.
Well, all this while I did try various combinations instead of just waiting for a response, such as - CHAR (10) only, or CHAR (13) only. But both resulted in same output. When I tried the combination of ASCII codes it did throw error as expected. So I was wondering if there is a syntax in case I have to use two ASCII codes.
ViRa wrote:Well, all this while I did try various combinations instead of just waiting for a response, such as - CHAR (10) only, or CHAR (13) only. But both resulted in same output.
Were you only checking the results in the cube viewer as opposed to in slices etc? You need to remember that the cube viewer is really more of a system object than a presentation layer (even though we sometimes use it that way.)
ViRa wrote:When I tried the combination of ASCII codes it did throw error as expected. So I was wondering if there is a syntax in case I have to use two ASCII codes.
How did you try and combine them?
A concatenation should work as it would any other way e.g.
How thorough is your testing? My guess is "not very".
Line feed Char(10) works fine as a line feed separator for strings stored in TM1 cubes for display on separate lines in Excel. Note that you do have to make sure that the wrap text property of the cells is set to true. Whether this is handled correctly in websheets I don't know but you would be welcome to test and let us know the result.
I don't think there is anything you can do about line wrapping in the cube viewer though, but the cube viewer is hardly a viewing interface for text data anyway. As Declan said, there wouldn't be much point combining the characters.
What you haven't said is what the display interface for users actually is. You can actually store rich text or markup in TM1 string cells. It will only display as plain text within TM1 but if you put a UI between the text and the user to handle text display and input then then it is possible to do a lot more with text intensive applications in TM1 than what has typically been done.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Declan, thanks for letting me know the correct way to concatenate multiple ASCII codes. I was definitely not using the syntax as yours and hence the error.
Lotsaram, I used the Line Feed code (10) only this time and tested the results in Cube View as well as Excel. Cube View displays the result in a single line and Declan's explanation to it being a system object than a presentation layer makes sense to me. On slicing to excel and Active Form, the address shows in a single line. Snapshot to Excel and ISB shows the result as I desired, i.e. variables are displayed one below the other.
Since the cube is being built for demo purpose, the users have not asked for any display interface in particular. I intend to use ISB for demo though.
Whether or not your thing is going to work is entirely dependent on whether the particular interface you are using supports the carriage return/line feed. It appears, not surprisingly, that the cube viewer does not. I know for a fact that Excel formulas do not unless you format the cell for line wrapping, this is why a slice or active form report doesn't work. This is easy to test. Simply type text in cell A1 and add a carriage return/line feed (by hitting Alt-Enter) and then typing in more text. The text wraps. Now go to another cell and make the formula be =A1. You'll see the text from A1 but the line wrap will be gone. Now format the cell for line wrapping and it's back.
Let me just add at this point that your exercise is a bad idea. Why? Because you are making your users think that wrapping the address is going to work in TM1 and it's not. It's ONLY going to work in an Excel-based report. It's better to be honest with your users up front. Don't be afraid to tell them something isn't going to work, it's not a reflection on you. It's only a reflection on you when you tell them something is going to work and it does not.
Thanks Tom for your advice. I will present the actual functionality in the demo rather than manipulating the variables (unless specifically asked for). This post helped me learn a new concept of concatenating variables which I always wanted to know. Also, I performed the test you suggested and now I understand it better. I enabled the wrap text feature and Active form as well as Slice now wraps the address as required.
