Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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
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 "-".
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;