Drop row if Substrings in Field A equals to Field B

[LiquidSword]

Hello! 

Suppose I have these two rows

• APPLE,ORANGE->ORANGE
• APPLE->ORANGE

I want to remove the first row from the table if the word after the arrow (i.e., orange) also appears before the arrow. The second row should be kept. There will always be one and only one word after the arrow. There might be up to 10 words before the arrow.

[marksouzacosta · Accepted solution]

Hi @LiquidSword ,

I have two solutions for that. One is smaller and easier to understand but there are some potential problems with it. The second solution is more complex but safe to use.

Solution 1

The problem with this solution if you have something like:
APPLE,ORANGE->ORANGE
APPLE->ORANGE
APPLE_ORANGE,APPLE->ORANGE *This record will be considered because it contains the word ORANGE. The complete term is APPLE_ORANGE but the SubStringCount ignores that. If that is your case, you have to use Solution 2.

LOAD
    FullLine
WHERE
	SubStringCount(SubField(FullLine,'->',1),SubField(FullLine,'->',-1)) = 0
;
LOAD * INLINE [
FullLine
APPLE,ORANGE->ORANGE
APPLE->ORANGE
](delimiter is '|');

Solution 2

LOAD
	FullLine
WHERE
	Match(FoundTermTotal,0)
;
LOAD
	FullLine,
    Sum(FoundTermCount) AS FoundTermTotal
GROUP BY
	FullLine
;
LOAD
	FullLine,
    Expression,
    SearchTerm,
    If(Match(Expression,SearchTerm),1,0) AS FoundTermCount
;
LOAD
    FullLine,
	SubField(SubField(FullLine,'->',1),',') AS Expression,
    SubField(FullLine,'->',-1) AS SearchTerm
;
LOAD * INLINE [
FullLine
APPLE,ORANGE->ORANGE
APPLE->ORANGE
](delimiter is '|');

Regards,

Mark Costa