Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

spavankumar1980
New Contributor II

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

 

IDRaterRating
X123S&PA+
X123MoodyAA
X123FitchAA+
Y123MoodyBB+
Y123D&PBB
Z123DBRSA+
Z123D&PA+
Z123S&PAAA
A123MoodyA+
B123KrollAA
B123D&PAaa

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

 

IDRaterRating
X123S&PA+
Y123MoodyBB+
Z123S&PAAA
A123MoodyA+
B123KrollAA

Can somebody help me how to get to this point .

Regards

Pavan

1 Solution

Accepted Solutions

Re: How to find a value based on condition

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

2 Replies
MVP
MVP

Re: How to find a value based on condition

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

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