Chart Expression Challenge

[#1]

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

[#2]

Could you give an example where the count is <>1

do we use ORDER_CDE like a key?

I'm not sure to understand

[#3]

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.

[#4 · Accepted solution]

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.

[#5]

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.