Record consolidation - Sparsely Populated Fields

dcohen13 — Thu, 23 Jun 2016 17:59:51 GMT

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

Re: Record consolidation - Sparsely Populated Fields

swuehl — Thu, 23 Jun 2016 22:39:26 GMT

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

(ooxml, embedded labels, table is Sheet2)

GROUP BY [Serial #];

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