I want to use query to filter data that matches another row with the data: [User Experience, Creative] and another row: [Creative].
I should appear in the query to match this 2 rows but currently by using split text into array.
I compare Array 1 contains Array 2 it will show if the data has the 2 text [Strategy, Creative] or only 1 text [Creative]
Yeah using relation it match 1 array to another array. But I need to do another match based on another set of data. Definitely more than 3 filters. e.g Hobbies, Gender, Location.
My current set up using the query column, what i encounter was data set A [Apple, Orange] matching to data set B [Apple, Orange, Banana] the result is false.
but if data set A [Apple, Orange] matching to data set B [Orange, Apple] the result is true a match.
I reference some related thread/topic.
which has the following JS. But i need help with adjusting to my desired outcome.
var arr = p1.split(‘,’);
var re = new RegExp(p2,‘g’);
var matches = ;
arr.forEach(function (item){
if (item.match(re)) {
matches.push(item);
}
});
return matches.join(‘,’);
Here a sample data which i want to filter A and B to find user that match them. I showed the result if its 1 data match but ideally i can have more data to filter to funnel down the match result.
- Username → Username in another table
- Numbers → User Data A (split)
- Colors → User Data B (split)
Create a Lookup column of the previously created JSON Template column (you’ll store the JSON of each row this way)
Create a Joined List column of the Lookup (to get a comma separated list the JS column will be able to read)
Create a JavaScript column to filter your data and get the expected output
let data = JSON.parse("[" + p1 + "]"); // to read and store the All joined JSON User Data
let filterNumbers = String(p2).split(","); // I had to force the String type because numbers are regarded as numbers... but it's not mandatory if you deal only with strings
let filterColors = p3.split(",");
let result = data
.filter(user =>
user.colors.some(color => filterColors.includes(color)) && // the .some means at least one element
user.numbers.some(number => filterNumbers.includes(number))
)
.map(user => user.username) // you retrieve the username value
.join(', '); // all matched usernames are joined
return result;
Here is my result with your sample data (the Gordon row is different because in my opinion, you should also get Gordon, he has a matching on “blue”):
And the previous JS code let you also play with a second filter, as you can see (it’s an at least “one of the colours” AND at least “one of the numbers” filter type):
If you have more filters to apply, just ajust the result variable by adding and ajusting another filter like that: "&& user.numbers.some(number => filterNumbers.includes(number))
Last observation: some steps I mentionned could have been done directly in JavaScript. However, I find it more easy to explain the logic this way. Feel free to improve it
Thank you Nicolas! Yes it works!
The javascript column we use, limits to P1 P2 and P3, which means it only accept up to 2 data to filter.
I am wondering, let says i have 5 data(or even more) to filter and find the suitable match. Can I join list for all 5 data as a string. Then filter this 1 string. Will it work this way, or are there alternatives.
And if you need another option, in additon to the relevant ones already mentioned by @Darren_Murphy and @ThinhDinh, you can go for a Template column where you write your code and replace as many parameters as you want.