I'm currently developing an application where I need to do a count. I'm currently using a match function but I need to find out if there is a better way to resolve this.
My main table looks like this:
Now I need to count the ID by matching the items in the Item column. The items I need to match are in another file like this:
The final result I want should be like this:
so to explain this, I need go through the main table and find all the distinct ID for item a2-p. Then I need to go through the main table again and count all the distinct ID for either item a2-p or u9-x. then I need to go through the main table again and count ID for either item a2-p or u9-x or a1-y and so on and so forth
for example here I've listed only 4 items but in reality I have a list of over 3000 items I need to go through. at the moment I'm suing somehting like this to do the count
if( match(ID,$(item), ID, null()) as CountID
where the value of $(item) changes with every iteration. For first iteration it's 'a2-p'. for second it's 'a2-p', 'u9-x' etc. once this is completed then I do the count on CountID.
Anyone has any thought on this approach? how can I do this for over 3000 items? Will match work for that many items? is ther a better approach? Can I use applymap or some other method to solve this more efficiently?