Not in formula filter on ETL

mhouk · 2023-10-06T16:06:01+00:00

There was an error rendering this rich post.

Hey, I am attempting to use the not-in function to filter out reversal charges.

I have a reference column and a memo column. The reference column has a transaction ID and I want to filter out the charge and the reversal.

I am writing a filter Memo Number not IN(Reference Number) but it only outputs the reversal only. see the attached document. I want to filter out both highlighted cells. Currently, it's only showing the red line.

Another way to explain is I want the take an array and say if a memo number matches any referance number then filter out both.

example.xlsx

example.

Find more posts tagged with

Magic ETL

Accepted answers

ColemenWilson

You'll have to do some work in ETL and join each memo row with each reference number so you can compare the two values on the same row. Once filtered, you can use a group by to return the data to the proper granularity to use in your card.

To join all data with all other data you'll need to use the add constants tile and add a "1" to every row of data then self join the data back to itself on the new constants column. This will essentially join each row with every other row so you can compare every row with every other row to check to see where there are the matches you are looking for.

All comments

ColemenWilson

MarkSnodgrass

If I am understanding the question, I think you might want to be using LIKE instead of IN. Using like with % signs on both sides of the field will make it look for that string anywhere within the other string. I tend to do this in a formula tile first and return a 1 or 0 and then use the filter tile to filter on 1 or 0. Here's an example where I look for a body part within a description field.

CASE WHEN LOWER(eventdescription) LIKE CONCAT('%', LOWER(bodypart) ,'%') THEN 1 ELSE 0 END

A few things to note. It is case sensitive so that is why I use the lower function to make it all lowercase. Since I am comparing one field to another, I need to use the CONCAT to build the % signs around the body part field.

Hope this helps.

mhouk

i have the numbers isolated through some text splitting but the issue im having is how it checks at the individual record level. Since the charge and reversal will never be on the same row i cant use = or like.

In the example below are two changes i want to to remove both since the reversal has the same transaction ID of the first reference row.

reference	memo
(Transaction #467038977)	charges etc text
(Transaction #467267003)	Reversal of transaction ID:467038977

ColemenWilson

Yep, so as I mentioned you will have to join each row with every other row to check both columns.

mhouk

just to add i am trying out Colemon's solution because i think that will work… i just feel like there should be a simpler solution than im seeing.

MarkSnodgrass

Colemen is correct. It will temporarily explode your dataset, but then you filter it back down. Magic ETL processes it pretty quickly, though.