Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I need help in finding out the Rating of a particular ID based on a condition.
Current table is in the below format
ID | Rater | Rating |
X123 | S&P | A+ |
X123 | Moody | AA |
X123 | Fitch | AA+ |
Y123 | Moody | BB+ |
Y123 | D&P | BB |
Z123 | DBRS | A+ |
Z123 | D&P | A+ |
Z123 | S&P | AAA |
A123 | Moody | A+ |
B123 | Kroll | AA |
B123 | D&P | Aaa |
Now , i want to find the "Rating" for a particular "ID" based on a below condition
So output i'm looking at is
ID | Rater | Rating |
X123 | S&P | A+ |
Y123 | Moody | BB+ |
Z123 | S&P | AAA |
A123 | Moody | A+ |
B123 | Kroll | AA |
Can somebody help me how to get to this point .
Regards
Pavan
Hi,
Try below code.
Data:
Load * inline [
ID, Rater, Rating
X123, S&P, A+
X123, Moody, AA
X123, Fitch, AA+
Y123, Moody, BB+
Y123, D&P, BB
Z123, DBRS, A+
Z123, D&P, A+
Z123, S&P, AAA
A123, Moody, A+
B123, Kroll, AA
B123, D&P, AAA
];
Left join (Data)
Load * inline [
Rater, Priority
S&P,1
Moody,2
Fitch,3
Kroll,4
DBRS,5
Egan Jones,6
D&P,7
];
Final:
Load ID as ID1, FirstSortedValue(Rating,Priority) as Rating1, FirstSortedValue(Rater,Priority) as Rater1
Resident Data Group by ID;
Regards,
Kaushik Solanki
you can add a table (Z2) to your data to sort the Rater
Z1:
load * inline [
ID, Rater, Rating
X123, S&P, A+
X123, Moody, AA
X123, Fitch, AA+
Y123, Moody, BB+
Y123, D&P, BB
Z123, DBRS, A+
Z123, D&P, A+
Z123, S&P, AAA
A123, Moody, A+
B123, Kroll, AA
B123, D&P, Aaa
];
Z2:
load * inline [
Rater, RaterOrder
S&P, 1
Moody, 2
Fitch, 3
Kroll, 4
AM Best, 5
DBRS, 6
Egan Jones, 7
D&P, 8
];
and these expressions in the chart:
FirstSortedValue(Rater, RaterOrder)
FirstSortedValue(Rating, RaterOrder)
Hi,
Try below code.
Data:
Load * inline [
ID, Rater, Rating
X123, S&P, A+
X123, Moody, AA
X123, Fitch, AA+
Y123, Moody, BB+
Y123, D&P, BB
Z123, DBRS, A+
Z123, D&P, A+
Z123, S&P, AAA
A123, Moody, A+
B123, Kroll, AA
B123, D&P, AAA
];
Left join (Data)
Load * inline [
Rater, Priority
S&P,1
Moody,2
Fitch,3
Kroll,4
DBRS,5
Egan Jones,6
D&P,7
];
Final:
Load ID as ID1, FirstSortedValue(Rating,Priority) as Rating1, FirstSortedValue(Rater,Priority) as Rater1
Resident Data Group by ID;
Regards,
Kaushik Solanki