Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have 2 fields in a table i want to rank each row based on alphabetically A-->Z letter.
The Data set is like this:
Load * Inline
[Client, Branch
Apple, Texas
Apple, Newyork
Apple, London
Beattle, Texas
Beattle, Munich
Orange, Boston
Orange, Callifornia
];
So in the script the resultant table will be
Client, Branch, Rank
Apple, Texas, 3
Apple, Newyork, 2
Apple, London, 1
Beattle, Texas, 2
Beattle, Munich, 1
Orange, Boston, 1
Orange, Callifornia, 2
Comments highly appreciated.
Try this:
LEFT JOIN ([Your table])
LOAD
Client,
Branch,
if(Client = peek('Client',-1),
peek('Rank', -1) + 1,
1) as Rank
RESIDENT [Your table]
ORDER BY Client, Branch
;
Try this:
LEFT JOIN ([Your table])
LOAD
Client,
Branch,
if(Client = peek('Client',-1),
peek('Rank', -1) + 1,
1) as Rank
RESIDENT [Your table]
ORDER BY Client, Branch
;
This works fine.
Thanks a lot for your valuable suggestions.