Help needed with an interesting count problem...

Anonymous — Tue, 08 May 2018 14:41:48 GMT

Hello experts,

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?

Thanks

I'm looking forward to your replies.

topic Help needed with an interesting count problem... in QlikView

Help needed with an interesting count problem...