How to Concatenate Strings with nested IF statements
I have four line items(Text1, Number1, Text2, Number2) I am trying to concatenate if those values are not blank or not equals to zero. See below formula. But I am getting formula invalid error. Please suggest how can i do concatenation of multiple string with nested IF statements.
Note: Please suggest me any Idea with only nested IF's not by creating system module or any other modules because i have a size limitation.
IF ISNOTBLANK(Text1) THEN Text1 ELSE
IF Number1>0 THEN & " " & TEXT(Number1) ELSE
IF ISNOTBLANK(Text2) THEN & " " & Text2 ELSE
IF Number2>0 THEN & " " & TEXT(Number2) ELSE BLANK
I would suggest, as a best practice, to limit the # of concatenations you're doing as a whole by centralizing all of the potential concatenations in a system module. This way your if statement is just referring to concatenations that have been calculated once as opposed to every single time the IF statement is being processed.
You could also use booleans to check if the fields are blank and use that in your statement as well to optimize it further.
Doing text concatenations with this intensity, is certainly against best practice but I may have a solution for you. Before diving into it, just wanted to ask if your team has access to some kind of ETL tool where perhaps as part of the data load, you could do this concatenation before the data arrives to Anaplan?
Here is the solution:
IF ISNOTBLANK(Text4) THEN Text 1 & " " & Text 2 & " " & Text 3 & " " & Text 4 ELSE
IF ISNOTBLANK(Text3) THEN Text 1&" " & Text 2 & " " & Text3ELSE
IF ISNOTBLANK(Text2) THEN Text 1 & " " & Text2ELSE
IF ISNOTBLANK(Text1) THENText1ELSE BLANK
Reasoning, I believe by reversing the order of the concatenations, this will run through all possible scenarios. Whereas in your original formula, if TEXT1 was not blank then the formula would take Text 1 and be done (I believe).
If anyone has a counter reason to flip the order back to original, happy to hear thoughts on that too. This is just my first glance gut reaction.
Also converting the IF ISNOTBLANK logic and storing them in Booleans would likely help the calculation perform better. But if space is a concern then the above would be the most concise way to do it. Though it would be slightly worse performance, in favor of saving space.
Curious if you need IF statements at all... Wouldnt it work to just do Text1 & Text2 & Text3 & Text 4? If any of them are blank, they will not have a value and will not combine anything. The IF Statements seem unnecessary.
It was my mistake to give all line items as text line items in my example . I have numeric line items as well to concatenate after changing them into text. It means my conditions are based on numeric value >0. I hope this clears the confusion on my requirement.
& (IF 'Number2' > 2 THEN " " &TEXT('Number2')ELSE BLANK)
This way, it is basically independent If Statements that get concatenated and calculate Blank if they are not met. Ideally, this could be staged out and concatenated in different lines.
If the space is avoidable in between the text bits I would encourage that as it would cut down drastically on the number of concatenations you are doing. You could even remove the 2nd If statement in that case and the formula would read:
&(IF 'Number1' > 0 THENTEXT('Number1')ELSE BLANK)
& (IF 'Number2' > 2 THEN TEXT('Number2')ELSE BLANK)