Not in formula filter on ETL

mhouk

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.

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.