Finding lowest ranking

[Chandu123456]

Hello Friends,

for each country to have more than one Ranking in the data need to find the lowest ranking name in the straight table.

for example:

Country, Rank1, Rank2, Rank3,lowest_Rank                                            

India,          aa,          bb,                cc,                    aa 

UK                dd          dd                  ee                     dd

another table:

Rank,Score

aa        1

bb        2

cc          3

dd         4

ee          5

1 is lowest rank for india country need to show aa in the lowest_rank column in the above table

4 is the lowest rank for UK country need to show dd in the lowest_rank column in the above table

[RsQK]

Hi, try this script:

temp_rank_values:
LOAD * INLINE [
Rank,Score
aa,1
bb,2
cc,3
dd,4
ee,5
];

temp:
LOAD * INLINE [
Country,Rank1,Rank2,Rank3    
India,aa,bb,cc
UK,dd,dd,ee
];

temp_min_rank:
CROSSTABLE(Rank_type,Rank,1)
LOAD * RESIDENT temp;

LEFT JOIN (temp_min_rank)
LOAD
Rank,
Score
RESIDENT temp_rank_values;

LEFT JOIN (temp)
LOAD
Country,
MIN(Score) as lowest_score
RESIDENT temp_min_rank
GROUP BY Country;

DROP TABLE temp_min_rank;

LEFT JOIN (temp)
LOAD
Score as lowest_score,
Rank as lowest_Rank
RESIDENT temp_rank_values;

DROP TABLE temp_rank_values;
DROP FIELD lowest_score;