1 Reply Latest reply: Jun 23, 2016 6:39 PM by Stefan Wühl RSS

    Record consolidation - Sparsely Populated Fields

    David Cohen
      Scope:
      I have many records for each unit, as they do a quality check list (Q1,Q2,Q3) at each station
      For simplicity, I want to consolidate each serial # into 1 record.
      May add more Q's over time, need it to be flexible


      screenshot.JPG

        • Re: Record consolidation - Sparsely Populated Fields
          Stefan Wühl

          You can use a GROUP BY clause and an appropriate aggregation function, maybe using something like

           

          LOAD [Serial #],

               Only(Q1) as Q1,

               Only(Q2) as Q2,

               Only(Q3) as Q3,

               Only(Q4) as Q4,

               Only(Q5) as Q5,

               Only(Q6) as Q6,

               Only(Q7) as Q7,

               Only(Q8) as Q8

          FROM

          [C:\Users\Stefan\Downloads\Record Consolidation.xlsx]

          (ooxml, embedded labels, table is Sheet2)

          GROUP BY [Serial #];

           

          Serial # Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
          100123YYYYYYYY
          100946YYYYYYYY
          154666YYYYYYYY