Drop row if Substrings in Field A equals to Field B

LiquidSword — Sun, 23 Jun 2024 23:27:42 GMT

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.

Re: Drop row if Substrings in Field A equals to Field B

marksouzacosta — Mon, 24 Jun 2024 00:28:26 GMT

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

topic Drop row if Substrings in Field A equals to Field B in App Development

Drop row if Substrings in Field A equals to Field B

Re: Drop row if Substrings in Field A equals to Field B