How to rank by multiple columns in a Calculated Column.

[arenti]

I would like to get a only 1 row as a table based on each [PRODUCT_ID], [CHECK_TYPE].

So I want to add a column calculated using Rank and then add a data table and convert it into a row filter for the final data.

[PRODUCT_ID], [CHECK_TYPE], [JUDGE_TYPE], [PROCESS_TIME]
1, A, N, 2024-04-15 => 2
1, A, Y, 2024-04-15 => 1
1, A, T, 2024-04-15 => 3
1, B, N, 2024-04-15 => 1
2, A, N, 2024-04-15 => 1
2, B, N, 2024-04-13 => 2
2, B, N, 2024-04-15 => 1

 

Sort by.

1. [JUDGE_TYPE] Calculated based on Y>N>H: CASE [JUDGE_TYPE] WHEN 'Y' THEN 1 WHEN 'N' THEN 2 WHEN 'H' THEN 3 ELSE 99 END
2. [PROCESS_TIME] is the most recent row if there is the same value of 1 
3. If it is the same as standard 2, baserowid() is the largest order => to filter out the final 1 row

 

I don't know how to calculate Rank based on two or more columns.
like 'ORDER BY CASE [JUDGE_TYPE] WHEN 'Y' THEN 1 WHEN 'N' THEN 2 WHEN 'H' THEN 3 ELSE 99 END, PROCESS_TIME, baserowid()'  in SQL

If there is any other good way to pick the final 1 row based on the criteria I want, please let me know that too.

Edited April 15 by arenti

[Gaia Paolini]

I understand (1)  the ranking of [JUDGE_TYPE].

I don't understand (2) the sorting of [PROCESS_TIME]. What do you mean by "if there is the same value of 1"?

I don't understand (3). What is the largest order? There is no order column.

Also, what do you mean by getting a 1 row table and what is the final row?
 

[arenti]

If [JUDGE_TYPE] is the same, the value of the same RANK will come out, but to prevent this, RANK with the [PROCESS_TIME] column. = 2

Even if do that, there may be the same RANK value, so i would like to prevent from having the same RANK value through BaseRowID(). = 3

Finally, I want to RANK according to the priority I want and remove all rows except 1 row each for [PRODUCT_ID] and [CHECK_TYPE]. (After adding the data table, I want to convert the row filter)

 

[PRODUCT_ID], [CHECK_TYPE], [JUDGE_TYPE], [PROCESS_TIME]
1, A, N, 2024-04-15 => 2 =Remove
1, A, Y, 2024-04-15 => 1
1, A, T, 2024-04-15 => 3=Remove
1, B, N, 2024-04-15 => 1
2, A, N, 2024-04-15 => 1
2, B, N, 2024-04-13 => 2=Remove
2, B, N, 2024-04-15 => 1

Edited April 16 by arenti

[Gaia Paolini]

To do it in steps:

First define a column [Decode_Judge_Type] to 'decode' JUDGE_TYPE like this:

case [JUDGE_TYPE] when 'Y' then 1 when 'N' then 2 when 'H' then 3 else 99 end

Then define a column [Rank_Process_Time] to rank PROCESS_TIME (most recent first) like this:

DenseRank([PROCESS_TIME],'desc',[PRODUCT_ID],[CHECK_TYPE])

[Note here that ranking can be done over multiple columns].

Then create your result like this:

case  
when ([Rank_Process_time]=1) and ([Decode_Judge_Type]=1) then 1 
when ([Rank_Process_time]=1) and ([Decode_Judge_Type]=Min([Decode_Judge_Type]) over ([PRODUCT_ID],[CHECK_TYPE])) then 1
 end

The empty columns can then be filtered out. See picture. Given the small example provided it looks ok, but I don't know if there are any fringe cases.

[arenti]

Is it right that Spotfire can't rank a Rank with multiple columns with one calculated column?

So should I rank each column by Rank or DECODE by each criteria and then get the final one row through MIN and OVER?

[Gaia Paolini]

Please let me know if the solution works. I don't know other ways in which to solve the issue, even though there probably are different ways to reach the same goal

[arenti]

Your solution works, But I want to know if there is any other way to do it.

Is it possible to obtain rank with multiple columns.

 

like this case.

If each person has a table with the number of gold, silver, and bronze medals.
No matter how many bronze medals there are, they are ranked lower than one silver medal, and no matter how many silver medals there are, they are ranked lower than one gold medal.
Is it possible to rank based on gold, silver, and copper columns? by Add 1 of calculted column.

 

Name | Gold | Silver | Bronze

Edited April 17 by arenti