Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
tamilarasu
Champion
Champion

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;

View solution in original post

4 Replies
tamilarasu
Champion
Champion

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;

Mark_Little
Luminary
Luminary

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

Anonymous
Not applicable
Author

Thanks, dear!

But if i have an example:

CityValue
City1a
City1b
City1-
City2by
City2d
City3zc
City3s

your code returns for city 1 "-".

tamilarasu
Champion
Champion

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;