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

    How to find a value based on condition

    Seshu S

      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

        • Re: How to find a value based on condition
          Massimo Grossi

          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
            Kaushik Solanki

            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