Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day!
In my source i have table:
City | Value |
City1 | |
City1 | |
City1 | a |
City1 | s |
City1 | f |
City1 | b |
City2 | b |
City2 | f |
City2 | b |
City3 | aaa |
City3 | jjh |
Following script get only firts Value for every City:
New:
LOAD
City,
firstvalue(Value)
FROM
(ooxml, embedded labels, table is Sheet1)
Group By City;
But for City1 it get missing value. I need for such rows get first value<>missing.
may be like this?
New:
LOAD
City,
firstvalue(Value)
FROM
(ooxml, embedded labels, table is Sheet1)
Group By City
and Value<>null()
and where Value<>null();
Try a where clause in your load script :
New:
LOAD
City,
firstvalue(Value)
FROM
test.xlsx
(ooxml, embedded labels, table is Sheet1)
where len(trim(Value)) > 0
Group By City
;
Hi Andre,
PFA. Hope it helps.
tab:
LOAD * INLINE [
City, Value
City1,
City1,
City1, a
City1, s
City1, f
City1, b
City2, b
City2, f
City2, b
City3, aaa
City3, jjh
];
NoConcatenate
tab1:
load
City,
FirstValue(Value) as Value
resident tab
where Value <>''
group by City;
Drop table tab;
Regards
KC