BAQ using Distinct

Kinetic ERP
1

I’ve been asked to create a BAQ that returns the top two scrap reasons by Part number. Sounded simple but apparently it’s NOT!

I have 9 Union SubQueries because of customized scrap reporting. We can record up to 10 scrap reasons in a single labor instance.
At the Top I group by Part Number and Reason code and sum the total scrap. I want it to return the top two scrap reasons for all parts. I had thought distinct top would do it but it only returns two part numbers. I’m sorting by part number then scrap qty.

thoughts?

2

You can try ranking them by part and then only return values 1 and 2. Here is a page that gives a lot of ideas:

2 Likes
3

Thanks for your help! — I’m NOT very good at Code so it took me a bit to get the syntex right but here is what I ended up with. I the Scrap Qty is a calculated field to sum by Part number and reason. It wouldn’t let me criteria for just 1 and 2 rankings because of the group by, so I threw it into a dashboard where I could filter the results. I’m sure there is a better way but this will serve the purpose! Thanks again for your help

image
image729×505 14.8 KB

4

If it works and is fast, then that should be good enough for now :blush:

5

instead of multiplying by -1 you can use use desc

DENSE_RANK() OVER (PARTITION BY Dtl10.JobHead_PartNum Order BY Qty DESC)
2 Likes
6

Thanks Brandon!