CODEWITHSUNDEEP
spotfirespotfire-analyst
Heather
Heather

Reputation: 989

Spotfire: Filtering table to get one record from each group

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'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!

Upvotes: 0

Views: 547

Answers (1)

Heather
Heather

Reputation: 989

I received an answer from the Tibco community on how to do this:

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.

Upvotes: 0

Related Questions