show selected case in field

[#1]

I have i table like this , i want to make a other table according th rule : For each item FILED 1, If it existes number of  U, then number of U, elso, number of S

INITIAL TABLE

FIELD 1	FIELD 2	NUMBER
1	S	7
1	U	6
2	S	5
2	U	-
3	S	4
3	U	9

FINAL TABLE

FIELD 1	NUMBER
1	6
2	5
3	9

How to proceed? thanks in advance

[#2]

Table1:

LOAD * INLINE [

   FIELD 1, FIELD 2, NUMBER

   1, S, 7

   1, U, 6

   2, S, 5

   2, U, -

   3, S, 4

   3, U, 9

];

table2:

load [FIELD 1] as field1,[NUMBER] resident Table1 where [FIELD 2]='U' and NUMBER <>'-';

table3:

load [FIELD 1],[NUMBER] resident Table1 where [FIELD 2]='S' and not Exists(field1,[FIELD 1]  );

drop table Table1;

Concatenate (table2) load [FIELD 1] as field1,[NUMBER] resident table3;

drop table table3;

[#3]

Thank you very muche David, your solution looks like work, but could you explain me why  :

[FIELD 2]='S' and not Exists(field1,[FIELD 1]  )

???

[#4]

if i have seval column NUMBER : NUMBER 1 , NUMBER 2, NUMBER3 in the initial table, and for the final table, the rule is the same : If it existes number1 of  U, then number1 of U, elso, number1 of S ; If it existes number2 of  U, then number2 of U, elso, number2 of S ...

how to play?

INITIAL TABLE

FIELD 1	FIELD 2	NUMBER1	NUMBER2	NUMBER3
1	S	7	23	5
1	U	6	56	18
2	S	5	78	20
2	U	-	55	30
3	S	4	44	77
3	U	9	-	-
4	S	5	7	24
4	U	7	-	-

FINAL TABLE

FIELD 1	NUMBER1	NUMBER2	NUMBER3
1	6	56	18
2	5	55	30
3	9	44	77
4	7	7	24

[#5]

This script is easier to understand than the one I wrote before,

Table:

LOAD * INLINE [

FIELD 1,    FIELD 2,    NUMBER1,    NUMBER2,    NUMBER3

1            ,S                ,7            ,23            ,5

1            ,U                ,6            ,56            ,18

2            ,S                ,5            ,78            ,20

2            ,U                ,-            ,55            ,30

3            ,S                ,4            ,44            ,77

3            ,U                ,9          ,-            ,-

4            ,S                ,5            ,7            ,24

4            ,U                ,7            ,-            ,-];

Table1:

load [FIELD 1],NUMBER1 as NUMBER1_U, NUMBER2 AS NUMBER2_U, NUMBER3 AS NUMBER3_U resident Table where [FIELD 2]='U';

join load [FIELD 1],NUMBER1 as NUMBER1_S, NUMBER2 AS NUMBER2_S, NUMBER3 AS NUMBER3_S resident Table where [FIELD 2]='S';

drop table Table;

Table2:

load *,

if(NUMBER1_U<>'-',NUMBER1_U,NUMBER1_S) AS NUMBER1,

if(NUMBER2_U<>'-',NUMBER2_U,NUMBER2_S) AS NUMBER2 ,

if(NUMBER3_U<>'-',NUMBER3_U,NUMBER3_S) AS NUMBER3

resident Table1;

DROP Table Table1;

drop fields NUMBER1_U,NUMBER1_S,NUMBER2_U,NUMBER2_S,NUMBER3_U,NUMBER3_S;