Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

andreyfcdk91
New Contributor III

Select only one first value for every id (new)

Good day!

In my source i have table:

  

CityValue
City1
City1
City1a
City1s
City1f
City1b
City2b
City2f
City2b
City3aaa
City3jjh

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.

3 Replies
balrajahlawat
Esteemed Contributor

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

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)

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
Honored Contributor II

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

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

Best Regards,
KC
Community Browser