topic Re: Select only one first value for every id in QlikView

Select only one first value for every id

Anonymous — Wed, 27 Jan 2016 08:22:34 GMT

Good day!

I have source table:

City	Value
City1	a
City1	b
City1	c
City2	by
City2	d
City3	zc
City3	s

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

City	Value
City1	a
City2	by
City3	zc

Re: Select only one first value for every id

tamilarasu — Wed, 27 Jan 2016 08:30:45 GMT

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;

Re: Select only one first value for every id

Mark_Little — Wed, 27 Jan 2016 08:31:43 GMT

Hi,

Maybe approach in script?

Load

City,

Value

IF(Rowno() = 1,

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

From '.....'

Group by City;

Then in the set just show where FirstValue = 1

Mark

Re: Select only one first value for every id

Anonymous — Wed, 27 Jan 2016 09:39:46 GMT

Thanks, dear!

But if i have an example:

City	Value
City1	a
City1	b
City1	-
City2	by
City2	d
City3	zc
City3	s

your code returns for city 1 "-".

Re: Select only one first value for every id

tamilarasu — Wed, 27 Jan 2016 10:11:40 GMT

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;