4 Replies Latest reply: Nov 8, 2018 4:15 AM by Wong Dehou RSS

    Aggr() ?

    Wong Dehou

      Hi all, below is my sample table:

       

      IDSTRMGPA
      115100
      115201
      115302
      216100
      216203
      216304

       

      Updated table:

      ID      Strm    Career      GPA
      1      1510        UG          0
      1      1520        UG          1
      1      1530        UG          2

      1      1610        PD          3

      1      1620        PD          4

      1      1630        PD          3.5

      2      1610        UG          0

      2      1620        UG          3

      2      1630        UG          4

      2      1710        PD          1

      2      1720        PD          5
      2      1730        PD          4

       

      EDIT:

      In my filter panel, I would like to a GPA filter for the maximum STRM, grouped by the ID. In this case, my desired output in the filter panel would have the GPA values '2' and '4' '3.5' and '4' . But there will be another filter for Career, so when the users select 'UG', the GPA filter pane should reflect the max(Strm) GPA for 'UG', in this case '2' and '4'.

       

      How do I achieve this?

       

      Thank you so much.

        • Re: Aggr() ?
          Martin Pohl

          a secret of good performance is to create datas in data model to reduce object calculation on sheets.

          so load

          YourData;

           

          left join load

          ID,

          max(STRM) as MaxSTRM

          resident YourData

          Group by ID;

          left join load

          ID,

          STRM as MaxSTRM

          ID as MaxGPA

          resident YourData;

          drop field MaxSTRM from YourData;

           

          So you will get a field MaxGPA within 2 for ID1 and 4 for ID2.

          Regards

          • Re: Aggr() ?
            Sunny Talwar

            Are you planning to make selection in this new filter that you create or is this just for display purposes?

            • Re: Aggr() ?
              Wong Dehou

              Nevermind, my requirement has changed, I would now display the max(strm) GPA by ID and Career.

              I did this in my loading script

              LATEST_STRM_GPA:

              LOAD ID ,CAREER,MAX(STRM) as STRM RESIDENT MAIN_TABLE

              where NOT ISNULL(GPA) group by EMPLID,ACAD_CAREER;



              LEFT JOIN (LATEST_STRM_GPA)

              LOAD ID,CAREER,STRM,LASTEST_GPA

              RESIDENT MAIN_TABLE

              where exists(STRM)and exists(ID);