How to find a value based on condition

[spavankumar1980]

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

1. Use S&P
2. If S&P unavailable, Use Moody
3. If  (S&P  and Moody) are unavailable, Use Fitch
4. If (S&P and Moody and Fitch) are unavailable, Use Kroll
5. if (S&P and Moody and Fitch and Kroll) are unavailable, Use AM Best
6. If unavailable, Use DBRS
7. If unavailable, Use Egan Jones
8. If unavailable, Use D&P

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

[maxgro]

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)

[kaushiknsolanki · Accepted solution]

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