Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dcohen13
Contributor III
Contributor III

Record consolidation - Sparsely Populated Fields

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

1 Reply
swuehl
MVP
MVP

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
100123YYYYYYYY
100946YYYYYYYY
154666YYYYYYYY