Filtering a dataset by an external list
Hi,
I'm trying to find a way to filter a data set using a separate list of items.
Here's the scenario:
My company is a wholesale distributor of products to retail stores. I'm analyzing sales data which contains customer (store), and details about the products sold to them, including brand, category, etc.
Some brands we sell are of particular interest to us, and others are being phased out, or exist only in historical records.
I would like to filter my dataset of all these purchases by a list of brands that we care about.
I wondered if there might be a way to establish a sort of array variable that I could update from time to time, but I couldn't find any documentation about using variables this way.
I could also upload a separate dataset containing the list of brands. But from there I'm not sure what recipe would help me utilize the list of "focus" brands to filter the larger dataset.
Anyone have any guidance on this?
Operating system used: MacOS Monterey v12.4
Best Answer
-
Ignacio_Toledo Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 411 NeuronOptionsHi @wjkelly
,If the list is long, and you have it on a text file to start with, it might be a good idea to upload the file as a new dataset, and then do an inner join with the dataset you want to filter, using the "Join with" recipe. When the brands change, you just need to change the text file, and update it on your project.
But also, you could use a solution with variables:
- Within your project, go to the "Project Variables" section and add the filter as json array:
- Now, go to the dataset you want to filter and select the visual recipe "Sample/Filter" and activate the filter option with this formula
(in the last screenshot I used the column "Band" as the one that needed to be filtered by the "brands" array... sorry for the typo).
Hope this helps!
- Within your project, go to the "Project Variables" section and add the filter as json array:
Answers
-
@Ignacio_Toledo
Thanks for that. The inner join did the trick really quick. I'll also explore the variable stored as a json array.