How to conditional remove duplicate and achieve below transformation ?

[prahlad_infy]

Hello Sir/Mam ,

Please suggest me the script to achieve below transformation where i am eliminating duplicates :

All those records which are having duplicates 

a) if the Flag 1 is having 1 and 0 then , 1 will take precedence and corresponding value1 and value2 to be loaded . example is Key = 1

b) if the Flag 1 is having 1 and 0 and for 1 no corresponding values as available then value1 and value2 should be loaded 

from row where Flag1 = 0 . example is Key=4

c) if the Flag 1 is having 1 and 0 and for flag1 , if corresponding values are in in any of the field (value1,value2) then that field value should be loaded . example is Key=5 .

Data :

LOAD * INLINE [

Key, Flag1, Value1, Value2
1, 1, 20, 30
1, 0, 30, 20
2, 0, 40, 50
3, 1, 20, 10
4, 1, ,
4, 0, 20, 60
5, 1, 50,
5, 0, , 80
];

Thank you all .

[prahlad_infy]

@tresesco @Kushal_Chawda 

Any help sirs 

[Kushal_Chawda · Accepted solution]

Data:
LOAD Key, 
     Flag1, 
     Value1, 
     Value2
FROM YourQVD
where Flag1=1 or not Exists(Key);

Left Join(Data)
LOAD Key, 
     Value1 as Value1_1, 
     Value2 as Value2_2
FROM YourQVD
where Flag1=0;

Final:
NoConcatenate
Load Key,
     if(len(Trim(Value1))>0 and (len(Trim(Value2))>0 or len(Trim(Value2))=0), Value1,
     if(len(Trim(Value1))=0 and (len(Trim(Value2))>0 or len(Trim(Value2))=0),Value1_1)) as Value1,
     if(len(Trim(Value2))>0 and (len(Trim(Value1))>0 or len(Trim(Value1))=0), Value2,
     if(len(Trim(Value2))=0 and (len(Trim(Value1))>0 or len(Trim(Value1))=0),Value2_2)) as Value2
Resident Data;

Drop Table Data;