1 Reply Latest reply: Nov 7, 2018 1:34 AM by Than Mughi RSS

    FirstSortedValue?

    Wong Dehou

      Hi all, below is my sample table:

       

      ID                             Career                                           Strm                             GPA         

      1M15300.000
      1M16105.000
      1M16204.750
      1U12200.000
      1U12304.000
      1U13103.860
      1U13203.860

             

      2P17303.500
      2P18103.830
      2P18203.830
      2P1830NULL
      2U12200.000
      2U12304.500
      2U13104.570
      2U13204.570

       

      For each ID, I want to select the GPA of the earliest STRM, grouped by the Career where GPA = '0'. Next, I would like to count the number of IDs.

       

      Desired output:

      ID                             Career                                           Strm                             GPA         

      1M15300.000
      1U12200.000

         

      2U12200.000

      Total count = 3

       

       

      I tried =FirstSortedValue(GPA,aggr(ID,[Strm],[Career])) as the GPA dimension but it did not work. Any ideas?


      Thank you so much in advance.

        • Re: FirstSortedValue?
          Petter Skjolden

          You could do it like this:

           

          LOAD

            ID,

            Career,

            Min(Strm) AS Strm,

            0 AS GPA

          INLINE [

          ID Career Strm GPA        

          1 M 1530 0.000

          1 M 1610 5.000

          1 M 1620 4.750

          1 U 1220 0.000

          1 U 1230 4.000

          1 U 1310 3.860

          1 U 1320 3.860

          2 P 1730 3.500

          2 P 1810 3.830

          2 P 1820 3.830

          2 P 1830 NULL

          2 U 1220 0.000

          2 U 1230 4.500

          2 U 1310 4.570

          2 U 1320 4.570

          ] (delimiter is spaces)

          WHERE

            GPA=0

          GROUP BY

            ID,Career;

          • Re: FirstSortedValue?
            Than Mughi

            Data:

            load * Inline

            [

            ID,Career,Strm,GPA         

            1,M,1530,0.000

            1,M,1610,5.000

            1,M,1620,4.750

            1,U,1220,0.000

            1,U,1230,4.000

            1,U,1310,3.860

            1,U,1320,3.860

            2,P,1730,3.500

            2,P,1810,3.830

            2,P,1820,3.830

            2,P,1830,NULL

            2,U,1220,0.000

            2,U,1230,4.500

            2,U,1310,4.570

            2,U,1320,4.570

            ];

             

             

             

             

            right join(Data)

            Data1:

            Load ID,Career,sum(Strm) as Strm

            RESIDENT Data WHERE GPA='0.000' Group by ID,Career;