Comparing text formatted line items

1635834

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.

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