Data filtering question

Greetings,
I’m working with a merged dataset whose data looks something like this


This dataset has multiple entries with the same “contactid” attribute. I wish to create a visual where I can filter out any data which do not have the “nomatch” attributename unless another piece of data with the same contactid has the “nomatch” attributename.

In other words, I would like to group together all data by contactid but remove any data if no instances of that contactid have have the “nomatch” attributename.

For example (using the above dataset), if I’m displaying the count for each contactid attribute in a bar chart, what I’d like to do, effectively, is remove the 9gsn38nf0wfd and 09u9n3brybyv bars from the visual because no instance of data with those contact ids have the nomatch value from the attributename attribute.

Please let me know if this is possible or if any clarification’s needed. Thx

Hi @GotoFarouj

Using calculated field we can find the no of records having “nomatch” grouped by contactid and you can apply the filter to remove it .

#no_of_nomatch_records_by_contact_id
sum(ifelse(attributename=‘nomatch’,1,0),[{contactid}])

Apply filter on this column to keep only contactids which are having atleast 1 .

image

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question.

Thanks
VInod

Hi @apjvinod

Thank you for taking the time to reply. Unfortunately, your solution doesn’t work because while it does partition which contactids have the nomatch attributename, it doesn’t count how many of that contactid exist in the dataset which is what I’m going for.

What I’m asking very well may not be possible and I may need to query differently through Athena, but I’m just checking to make sure.