Announcements
cancel
Showing results for
Did you mean:
Partner - Creator II

## How to conditional remove duplicate and achieve below transformation ?

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 :

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 .

Labels (1)
• ### Qlik Sense

1 Solution

Accepted Solutions

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

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

Final:
NoConcatenate
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;

2 Replies
Partner - Creator II
Author

@tresesco @Kushal_Chawda

Any help sirs

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

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

Final:
NoConcatenate