Help with rank transformation

[jisoo23]

I'm having some trouble using a rank transformation in my mapping that I hope someone can help me with. Here's a simple example of the record set being fed in:

code_no     command     version_no
001-001     D           1
001-001     D           2
002-002     A           1
002-002     A           2
003-003     A           0
004-004     D           3

Basically what I'm trying to do is in cases where a code_no has multiple instances, I want to keep the record with the smallest version number. So in this set of data, the second and fourth rows are excluded and all others left alone. I tried using a rank transformation where I marked code_no and command each with "group-by" and marked version_no as the "rank value". Then in properties set it to "bottom" and "Number of Ranks" to 1. But this set-up only gives me 1 record, not all the rest of the records excluding the highest version_no records. Can anyone tell me what I'm doing wrong? I'm running version 7.1 if that helps...

Thanks,
JIsoo23

 

[blom0344]

You are almost out of the woods on this one.
You are misinterpeting the number of ranks setting. If you set this to 1 it means that for every group there is only ONE rank value available.
What will work is just apply the group by on the 'code-no', use a higher rank-setting and then use a filter transformation after the ranking to only pass the rows with rank-index 1.

Ties Blom
Information analyst