topic Select only one first value for every id (new) in QlikView

Select only one first value for every id (new)

Anonymous — Wed, 27 Jan 2016 09:53:57 GMT

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.

Re: Select only one first value for every id (new)

Anonymous — Wed, 27 Jan 2016 10:01:50 GMT

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

Re: Select only one first value for every id (new)

Anonymous — Wed, 27 Jan 2016 10:02:46 GMT

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

;

Re: Select only one first value for every id (new)

jyothish8807 — Wed, 27 Jan 2016 10:09:21 GMT

Hi Andre,

PFA. Hope it helps.

tab:

LOAD * INLINE [

City, Value

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