Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I received a request to analyze rejects per order for quality control purposes. To keep it simple, I have two columns in a table that shows by order how many rejects were noted:
ORDER_CDE | TYPE_CODE |
101 | Q1 |
101 | K1 |
101 | K2 |
101 | K3 |
101 | R1 |
102 | Q2 |
102 | K4 |
102 | K5 |
102 | K6 |
103 | R2 |
103 | Q3 |
103 | K9 |
103 | K10 |
103 | K11 |
103 | R15 |
103 | R16 |
My challenge is to report for every order code that has a Q type code, which K codes and how many need to be reported.
The layout would look like this. In other words they want to see for every Q reject in an order, how many K rejects are there. Note: there can be many other rejects lik R1, R2 so this can get complicated. I tried set analysis but couldn't get it to work. I imagine if you have two sets: orders with Q and orders with K you can get some type of intersection. I need to exclude the other type codes other than K.
Q Code | K Code | Count |
Q1 | K1 | 1 |
Q1 | K2 | 1 |
Q1 | K3 | 1 |
Q2 | K4 | 1 |
Q2 | K5 | 1 |
Q2 | K6 | 1 |
Q3 | K9 | 1 |
Q3 | K10 | 1 |
Q3 | K11 | 1 |
Not sure how to solve this. Does anyone have a solution? Thanks.
Don
Maybe there is a better way, but It's working.
I just added 102,K4 to check the count.
Let me know if it's not that you expect.
Could you give an example where the count is <>1
do we use ORDER_CDE like a key?
I'm not sure to understand
Thank you Eva. Yes ORDER_CDE and TYPE_CDE are both keys. I basically want to count the number of orders with a Q TYPE_CDE that also have a K TYPE_CDE. Keep in mind that there can be other TYPE_CDE s other than Q or K. Hope this helps. Please let me know if you need more.
Maybe there is a better way, but It's working.
I just added 102,K4 to check the count.
Let me know if it's not that you expect.
Eva, thank you so much for looking into this. I tried to use a calculated dimensions for Type_Code = Q and Type_Code = K, but it didn't work. It looks like I will have to do this in the load script like you did.