Filter table to get one record from each group

[Guest]

I have the following table:

| UWI | FORM | SOURCE | MD || --- | ---- | ------ | -- || 123 | BRAIDED | DRR | 100 || 123 | BRAIDED | ERK | 150 || 123 | BRAIDED | KPB | 200 || 123 | TUSCHER | DRR | 300 || 123 | TUSCHER | MDB | 350 || 123 | TUSCHER | KPB | 375 || 456 | BRAIDED | DRR | 150 || 456 | BRAIDED | KPB | 275 || 456 | TUSCHER | BTM | 500 || 456 | TUSCHER | DRR | 550 || 456 | TUSCHER | ERK | 525 |

All columns are string columns except MD, which is Real, however no math is required.

I need to filter the data so that only one row for a particular UWI and formation are returned based on a set hierarchy of the SOURCE.

The hierarchy is:

1) ERK

2) MDB

3) DRR

4) KPB

5) BTM

What I'm looking for is a table that looks like this once the filter is applied:

| UWI | FORM | SOURCE | MD || --- | ---- | ------ | -- || 123 | BRAIDED | ERK | 150 || 123 | TUSCHER | MDB | 350 || 456 | BRAIDED | DRR | 150 || 456 | TUSCHER | ERK | 525 |

What is the best way to accomplish this? I thought about adding a DenseRank calculated column, which will group the rows by UWI and FORM using SOURCE for the ranking, but there are two problems with that:

- I don't know how to assign a particular rank number to a particular source; and

- If I take the resulting ranking based on the alphabet, if I add a new source (let's say KMP just under ERK) it will mess up some of the other ranking numbers.

I've thought of using a Case statement, but I'm not sure how to group the UWI and FORM (I know about OVER, but every example I've found requires some kind of number column to do an aggregation).

This filter will be applied just after import.

Any direction would be greatly appreciated!

[Gaia Paolini]

Try this approach:

On top menu:

Data > Column Properties > select SOURCE 

Choose Sort Order tab.

Choose Custom Sort Order and configure the desired order

press OK

Now define the calculated column [sourceRank] as

DenseRank([sOURCE],Concatenate([FORM],[uWI]))

The filter can then be applied to [sourceRank]=1.

[Guest]

Gaia, thank you so much for this! It is exactly what I was looking for.