Comparing text formatted line items

1635834
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.

 

ListError 1Error 2BooleanOutput
Product 1Text 1,Text 4Text 1,Text 4FALSE 
Product 2Text 1,Text 4Text 4,Text 1FALSE 
Product 3Text 4,Text 5Text 1,Text 4TRUEText 5
Product 4Text 4Text 4FALSE 
Product 5Text 4Text 1TRUEText 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.

8 REPLIES 8
Misbah
Moderator

@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
Contributor

@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
Moderator

@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

1635834
Contributor

@Misbah 

 

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

alexpavel
Certified Master Anaplanner

@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

MarkWarren
Expert

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
Certified Master Anaplanner

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):

ListError 1Error 2BooleanOutput
Product 1Text 1,Text 4 TRUEText 1,Text 4
Product 2 Text 4TRUE 

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
Super Contributor

Hi 

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

Solution 1

ManjunathKN123_0-1642322726001.pngManjunathKN123_1-1642322740191.png

Solution 2

ManjunathKN123_2-1642322767613.png

ManjunathKN123_3-1642322789076.png

 

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