topic Re: group by and first value not null in App Development

group by and first value not null

flo2 — Wed, 20 Oct 2021 09:46:56 GMT

Hello,

I have a little problem i wonder how to solve.

I have a table like this

Num Employee	Is he vegetarian	Does he like chicken
1	Yes
1		Yes
2	Yes
3	Yes

And i would like to group by Employee and have the values of the two other columns in the same row.

Num Employee	Is he vegetarian	Does he like chicken
1	Yes	Yes
2	Yes
3	Yes

I tried Firstvalue and firstsortedvalue distinct but using this I have empty value at third column for the Employee 1 cause the first value of the third field is empty

( i need to do it in script )

If i could have some indications / ideas

thanks a lot for reading, have a nice day

Re: group by and first value not null

Or — Wed, 20 Oct 2021 10:05:20 GMT

Load [Num Employee], Max([Is he vegetarian]) as [Is he vegetarian], max([Does he like chicken]) as [Does he like chicken]

Resident FirstTable

Group by [Num Employee];

Re: group by and first value not null

flo2 — Wed, 20 Oct 2021 10:15:07 GMT

Thanks for reply,

But the result is a whole empty table

is it not because max is just for numeric values ?

EDIT : I used maxString it seems ok, i would suit no ?

Re: group by and first value not null

Or — Wed, 20 Oct 2021 10:20:25 GMT

I didn't actually run the script, so that may be the case. Any function that returns a (string) value should work here, including FirstSortedValue, Only, and Mode.

I actually ran it with Only and it works fine (I had to use if() statements to replace the blanks with actual nulls):

Load [Num Employee], Only([Is he vegetarian]) as [Is he vegetarian], Only([Does he like chicken]) as [Does he like chicken]
Group by [Num Employee];
Load [Num Employee], if(len([Is he vegetarian])>0,[Is he vegetarian]) as [Is he vegetarian], if(len([Does he like chicken])>0,[Does he like chicken]) as [Does he like chicken]
INLINE [
Num Employee, Is he vegetarian, Does he like chicken
1, Yes,
1 , , Yes
2, Yes,
3, Yes, ] ;

Re: group by and first value not null

JGMDataAnalysis — Wed, 20 Oct 2021 20:28:26 GMT

Another possible solution:

Temp: NOCONCATENATE LOAD * INLINE [ Num Employee, Is he vegetarian, Does he like chicken 1, Yes, 1,,Yes 2, Yes, 3, Yes, ]; Test: NOCONCATENATE LOAD [Num Employee], [Is he vegetarian] RESIDENT Temp WHERE Len([Is he vegetarian]) > 0 ; JOIN (Test) LOAD [Num Employee], [Does he like chicken] RESIDENT Temp WHERE Len([Does he like chicken]) > 0 ; DROP TABLE Temp;