Select only one first value for every id (new)

[Anonymous]

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.

[Anonymous]

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();

[Anonymous]

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

;

[jyothish8807]

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