topic How to find a value based on condition in App Development

How to find a value based on condition

spavankumar1980 — Sun, 13 Nov 2016 15:01:50 GMT

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

Use S&P
If S&P unavailable, Use Moody
If (S&P and Moody) are unavailable, Use Fitch
If (S&P and Moody and Fitch) are unavailable, Use Kroll
if (S&P and Moody and Fitch and Kroll) are unavailable, Use AM Best
If unavailable, Use DBRS
If unavailable, Use Egan Jones
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

maxgro — Sun, 13 Nov 2016 16:40:19 GMT

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)

Re: How to find a value based on condition

kaushiknsolanki — Sun, 13 Nov 2016 16:44:52 GMT

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