2 Replies Latest reply: Nov 13, 2016 11:44 AM by Kaushik Solanki

How to find a value based on condition

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

• Re: How to find a value based on condition

you can add a table (Z2) to your data to sort the Rater

Z1:

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:

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)

• Re: How to find a value based on condition

Hi,

Try below code.

Data:

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)

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