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.

please help me with this.

Regards

Shirisha.

Accepted answers

All comments

Misbah

@1635834

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

1635834

@Misbah

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.

Misbah

@1635834

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

MarkWarren

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.

M.Kierepka

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.

ManjunathKN

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

1635834

@Misbah

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

alexpavel

@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

Quick Links

Can you help?

Unanswered questions

Missing Column Headers in Anaplan Export When Using API
Hi Community, I’m encountering an issue where an export created in Anaplan is missing column headers when retrieved via the API. However, when I manually run the same export from the Actions tab, the downloaded file includes the headers. Has anyone experienced this behavior before? I’d appreciate any insights into the root…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385