Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

dcohen13
New Contributor II

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

Tags (1)
1 Reply
MVP
MVP

Re: Record consolidation - Sparsely Populated Fields

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