Contributor

## Comparing text formatted line items

Hi Team

I am having a module with two line items(Error 1, Error 2). I need boolean to be checked if the texts in two line items are not same and it should retrieve the text which is not same in another line item.

 List Error 1 Error 2 Boolean Output Product 1 Text 1,Text 4 Text 1,Text 4 FALSE Product 2 Text 1,Text 4 Text 4,Text 1 FALSE Product 3 Text 4,Text 5 Text 1,Text 4 TRUE Text 5 Product 4 Text 4 Text 4 FALSE Product 5 Text 4 Text 1 TRUE Text 4

Note: Text 1,Text 4 and Text 4,Text 1 are same. So it should return false.

Regards

Shirisha.

8 REPLIES 8
Moderator

## Re: Comparing text formatted line items

There is a function "COMPARE" which can compare Texts but that will not tell you what is the difference, it is only going to tell you that there is a difference by returning values like -1,0 or 1.

You can leverage COMPARE function with other functions to get to your result

Hope that helps

Misbah

Miz Logix

Contributor

## Re: Comparing text formatted line items

I used COMPARE() function before, but it is not taking (Text 1,Text 4) and (Text 4,Text 1) as same.

In this scenario it should consider as both are same. So, I can't use COMPARE() function.

Regards

Shirisha.

Moderator

## Re: Comparing text formatted line items

Is it always going to be two texts in a line or could it be any number of texts? And if any then what would be the maximum number of texts that you may receive

Misbah

Miz Logix

Contributor

## Re: Comparing text formatted line items

It can be any number of texts not only two. It can contain maximun of 35 number of texts.

Certified Master Anaplanner

## Re: Comparing text formatted line items

@1635834  The Comparison of the text values is easy to achieve.. Not sure if you could have also cases like  "Text1, Text2" to compare with "Text2, Text1" ..so the order of the texts in Error1 and Error2 line-items to be different. In this case, the COMPARE() function will return that the texts are not equal.

The big challenge is to extract what is the difference between Error1 and Error2 in the "Output" line-item... I do not know an easy and dynamic way to solve it within 1 module.

You said that the Text values are quite limited (35 possible values).

You will need a new list with 35 elements: 35List.

You can create a system module for the 35List with a Text Line-item and associate per every element all possible values for every single Error value: Text 1, Text 2, Text 3, etc.

You can build a calculation module with Product and 35List in "Apply To".

In this module, you can create formulas using the FIND() function to calculate booleans for Error1 and Error2 values from the initial module.

Based on the comparisons of the booleans, you can extract per every value of the 35List if Error1 and Error2 are different or equal and you can summarize the differences in the "Output" line-item using the TEXTLIST() function.

Hope it helps

Alex

Expert

## Re: Comparing text formatted line items

Could you try using booleans to replace the Error text, have booleans Error1, Error2 etc. If you have a known number of errors. Or use a list and list formatted line item, again relies on a known number of errors and fixed error combinations.

Certified Master Anaplanner

## Re: Comparing text formatted line items

Hi,

Do you have to do it on text values? If you can introduce Errors as dimension there, it will be much easier to determine which one is 1st line item and is not in 2nd one - just use boolean flags to determine which text is present in Error 1 and 2, and then "ERROR 1 AND NOT ERROR 2" formula should give you "true" for error you wanted to detect.

Apart from questions asked by other, few other examples to clarify from me (if I understand problem correctly):

 List Error 1 Error 2 Boolean Output Product 1 Text 1,Text 4 TRUE Text 1,Text 4 Product 2 Text 4 TRUE

If you can't introduce Errors dimension, you might want to encode them from text to numeric flags. Let me show how it can be done:
Error 1: change text 2, text 4, text 6 to 010101
Error 1: change text 1, text 2, text 6 to 220002
Then add Error 1 to Error2 = 230103
Positions with "1" are codes of text that is in Error 1 but not Error 2, so in this case it's position 4, so text 4.
TBH I recommend counting positions from the end, then you can use POWER and MOD functions.

Occasional Contributor

## Re: Comparing text formatted line items

Hi

I have given 2 solutions for you.
you can use any one of them as per your use case.

Solution 1

Solution 2

In the solution 2 you need to break the line items, if you try to merge by if else you will get circular reference error. you can hide the other line items when displaying.

Hope this helps.

Thanks,
Manjunath