Merge rows with common fields and verifying conditions

[#1]

Hi

I wish to write a clean up script that merges fields that verify

1/ fields A and B are common to both rows

2/ fields C and D are N/A for one row and equals field B for the other row

Ex.

A              B                C              D

Val1          Val2           N/A           N/A
Val1          Val2           Val2          Val2

then in the first row, fields C and D should be filled with value Val2.

I cannot find out how to write a condition on two rows (or write a condition with a search on the other rows inside)

Thanks

[maxgro]

Hi

for 2 rows you can try something similar to

Tmp:

LOAD * INLINE [

A   ,           B    ,            C    ,          D,

Val1,          Val2,           N/A,           N/A

Val1,          Val2 ,          Val2,          Val2

];

Table:

load

*,

if(Peek(A)=A and Peek(B)=B and C='N/A' and D='N/A' and peek(C)=Peek(B) and peek(D)=Peek(B), Peek(C), C) as NewC,

if(Peek(A)=A and Peek(B)=B and C='N/A' and D='N/A' and peek(C)=Peek(B) and peek(D)=Peek(B), Peek(D), D) as NewD

resident Tmp

order by A, B, C desc, D desc;

DROP Table  Tmp;

but what if you have more than 2 rows? example

A              B                C              D

Val1          Val2           N/A           N/A
Val1          Val2           Val2          Val2

Val1          Val2           N/A           N/A

which result do you want?

[#3]

thanks, this is hepful.

then, i would like all three rows be replaced when needed by Val1          Val2           Val2          Val2

[maxgro]

Try this;

perhaps you have to change N/A in something else to be sure the order by works

Tmp:

LOAD * INLINE [

A  ,          B    ,            C    ,          D,

Val1,          Val2,          N/A,          N/A

Val1,          Val2 ,          Val2,          Val2

Val1,          Val2,          N/A,          N/A

Val1,          Val3,          N/A,          N/A

Val1,          Val3 ,          Val3,          Val3

Val1,          Val3,          N/A,          N/A

];

Table:

noconcatenate load

rowno(),

A,

B,

if(Peek(A)=A and Peek(B)=B and C='N/A' and D='N/A' and peek(C)=Peek(B) and peek(D)=Peek(B), Peek(C), C) as C,

if(Peek(A)=A and Peek(B)=B and C='N/A' and D='N/A' and peek(C)=Peek(B) and peek(D)=Peek(B), Peek(D), D) as D

resident Tmp

order by A, B, C desc, D desc;

DROP Table  Tmp;