if you want to choose the row with minimum Yearmonth2
source:
LOAD
Yearmonth & '-' & [Percent of pass] as Field,
Yearmonth,
[Percent of pass],
Grade,
Teach,
Yearmonth2,
[Percent of pass2],
Grade2,
Teach2,
Yearmonth3,
[Percent of pass3],
Grade3,
Teach3,
Yearmonth4,
[Percent of pass4],
Grade4,
Teach4
FROM
SampleData.xlsx
(ooxml, embedded labels, table is Sheet1);
table:
NoConcatenate load
*
Resident source
where peek(Field) <> Field
order by Yearmonth, [Percent of pass], Yearmonth2
;
DROP Table source;
another one: if you want to choose using different logic for different fields you can use group by
LOAD Yearmonth,
[Percent of pass],
MinString(Grade) as Grade,
MinString(Teach) as Teach,
min(Yearmonth2) as Yearmonth2,
min([Percent of pass2]) as [Percent of pass2],
MinString(Grade2) as Grade2,
MinString(Teach2) as Teach2,
max(Yearmonth3) as Yearmonth3,
min([Percent of pass3]) as [Percent of pass3],
MinString(Grade3) as Grade3,
MinString(Teach3) as Teach3,
min(Yearmonth4) as Yearmonth4,
min([Percent of pass4]) as [Percent of pass4],
MinString(Grade4) as Grade4,
MinString(Teach4) as Teach4
FROM
SampleData.xlsx
(ooxml, embedded labels, table is Sheet1)
group by
Yearmonth,
[Percent of pass]
;
