I would appreciate some assistance to extract a table from a transaction file. I want to do all the calculations in the front end. The transaction file has many fields, but the ones of interest are a value field, a CPT Code field and an Authorisation field. Each CPT value is made up of several authorisation, and each authorisation is made up of many transactions. The following table will explain the scenario better that trying to describe it. The required output is the CPT code and the average of Authorisations associated with that CPT. The issue at hand is that I only want to include transactions that fall within +95% and -95% of the original average. You will see that the original average is 494, but if we exclude the 2 values that fall outside our parameters (10 and 2,000) the new average is 366.
| CPT |
Auth No |
Sub Total (From Many Transactions) |
Include In Average |
| A |
1 |
100 |
Y |
| A |
2 |
200 |
Y |
| A |
3 |
400 |
Y |
| A |
4 |
500 |
Y |
| A |
5 |
700 |
Y |
| A |
6 |
10 |
N |
| A |
7 |
2,000 |
N |
| A |
8 |
230 |
Y |
| A |
9 |
300 |
Y |
| A |
10 |
500 |
Y |
| |
|
|
|
| |
Average |
494 |
|
| |
Min (95%) |
25 |
|
| |
Max (95%) |
963 |
|
| |
|
|
|
| Required Output |
|
|
| CPT |
|
Value |
|
| A |
|
366 |
|
