Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
waqqas2426
Contributor

Help needed with an interesting count problem...

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:

tab1.PNG

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:

tab2.PNG

The final result I want should be like this:

tab3.PNG

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.



Tags (1)