Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
andreyfcdk91
New Contributor III

Select only one first value for every id

Good day!

I have source table:

  

CityValue
City1a
City1b
City1c
City2by
City2d
City3zc
City3s

I need to get result table, which will have only one firts value for every City:

  

CityValue
City1a
City2by
City3zc
1 Solution

Accepted Solutions

Re: Select only one first value for every id

Hi,

Use FirstValue() syntax in your load script.

Temp:

LOAD * INLINE [

    City, Value

    City1, a

    City1, b

    City1, c

    City2, by

    City2, d

    City3, zc

    City3, s

];

NoConcatenate

Data:

Load City,

FirstValue(Value) as Value

Resident Temp Group by City;

DROP Table Temp;

4 Replies

Re: Select only one first value for every id

Hi,

Use FirstValue() syntax in your load script.

Temp:

LOAD * INLINE [

    City, Value

    City1, a

    City1, b

    City1, c

    City2, by

    City2, d

    City3, zc

    City3, s

];

NoConcatenate

Data:

Load City,

FirstValue(Value) as Value

Resident Temp Group by City;

DROP Table Temp;

mark6505
Valued Contributor III

Re: Select only one first value for every id

Hi,

Maybe approach in script?

Load

     City,

     Value

     IF(Rowno() = 1,

          1,

          IF(PEEK(City),-1 <> City,1,) as FirstValue

From '.....'

Group by City;

Then in the set just show where FirstValue = 1

Mark

andreyfcdk91
New Contributor III

Re: Select only one first value for every id

Thanks, dear!

But if i have an example:

CityValue
City1a
City1b
City1-
City2by
City2d
City3zc
City3s

your code returns for city 1 "-".

Re: Select only one first value for every id

Hi Andrey,

Try something like below

Temp:

LOAD * INLINE [

    City, Value

    City1,

    City1, b

    City1, a

    City2, by

    City2, d

    City3, zc

    City3, s

];

NoConcatenate

Data:

Load City,

FirstValue(Value) as Value

Resident Temp where len(Trim(Value))>0 Group by City ;

DROP Table Temp;

Community Browser