2 Replies Latest reply: Aug 23, 2016 1:12 PM by Vivek Nair RSS

    Aggregation?

    John Simmons

      I'm loading data from a file like so:

       

      LOAD Dept, Clinic, Meprs FROM [excel file on disk];

       

      The data looks like this:

       

      Dept1, Clinic1, Meprs1
      Dept1, Clinic1, Meprs2
      Dept1, Clinic1, Meprs3
      Dept1, Clinic2, Meprs4
      Dept1, Clinic2, Meprs5
      Dept1, Clinic3, Meprs6
      Dept1, Clinic3, Meprs7
      Dept1, Clinic3, Meprs8
      Dept1, Clinic3, Meprs9
      Dept2, Clinic4, Meprs10

      ...and so on

       

      I want to create the following table

       

      Dept1, Clinic1, "Meprs1,Meprs2,Meprs3"
      Dept1, Clinic2, "Meprs4,Meprs5"
      Dept1, Clinic3, "Meprs6,Meprs7,Meprs8,Meprs9"
      Dept2, Clinic4, "Meprs10"

      ...and so on

       

      How would I go about doing that?

        • Re: Aggregation?
          John Simmons

          Never mind - I fingered it out:

           

          NoConcatenate
          PC_Clinics_2:
          LOAD Dept,
          Clinic,
          CONCAT(MEPRS,',') AS MEPRSGroup
          RESIDENT PC_Clinics
          GROUP BY Dept,ClinicName;

          • Re: Aggregation?
            Vivek Nair

            temp:
            LOAD * INLINE [
            F1, F2, F3
            Dept1, Clinic1, Meprs1
            Dept1, Clinic1, Meprs2
            Dept1, Clinic1, Meprs3
            Dept1, Clinic2, Meprs4
            Dept1, Clinic2, Meprs5
            Dept1, Clinic3, Meprs6
            Dept1, Clinic3, Meprs7
            Dept1, Clinic3, Meprs8
            Dept1, Clinic3, Meprs9
            Dept2, Clinic4, Meprs10
            ]
            ;


            LOAD F1,F2,Concat(F3,',')
            Resident temp
            GROUP BY  F1,F2