TL:DR Filter values from a list out of a dataset
Hi community,
I have a dataset of transactional data that has a column of unique provider keys. I also have a blacklist of provider keys that I need to filter from that dataset. Is there a fast and efficient way of removing say (200-400) providers from a data cube through some sort of filter transform?
Thanks!
There is a filter transform that could be used in a cube. You can select a specific column and configure the filter to ‘not contains’ and that would exclude the blacklist
The question is how to get the blacklist data in to the cube. If you can get a new column in the original data source that contains ‘no’ for each blacklist entry, then you could just exclude those rows in the filter.
Otherwise, I would create a separate file containing the blacklisted keys in column A, and the word ‘no’ in column B. Then do a lookup transform from the main data source to pull in the ‘no’ or blank values from this column to the cube. Then do the filter transform after that.
Thanks - the lookup transform sounds like the most feasible solution. Manually copying and pasting ~250 lines does not sound like a fun day!
Worked like a charm! Thanks so much for the suggestion
Great to hear.
Anything is better than manually copying and pasting! 