Issue removing duplicates
Moderators: chulett, rschirm, roy
Issue removing duplicates
Hi All,
I have 2 input files. I want to compare two files and remove duplicates from the 2nd file and merge the files into single file.
input1
Empno | Ename
101 | ABC
102 | BBB
Input2
Empno
101
103
Output
Empno|Ename
101 | ABC
102 |BBB
103
I have 2 input files. I want to compare two files and remove duplicates from the 2nd file and merge the files into single file.
input1
Empno | Ename
101 | ABC
102 | BBB
Input2
Empno
101
103
Output
Empno|Ename
101 | ABC
102 |BBB
103
Code: Select all
If IsNull(dslink.left.empno) or dslink.left.empno = 0 then dslink.right.empno else dslink.left.empno
If this doesn't work then take the output of left.empno and right.empno into peek stage. See if the join in putting zero or null
In the join stage use hash partitioning sort ascending on empno field on both input links.
Then in transformer use below derivation :
Then in transformer use below derivation :
Code: Select all
If Trim(dslink.left.empno) = '' or IsNull(dslink.left.empno) or dslink.left.empno = 0 then dslink.right.empno else dslink.left.empno
-
- Participant
- Posts: 18
- Joined: Tue Dec 25, 2012 10:39 pm
- Location: Detroit,MI
I think in your case you are getting Nulls for all the fields because of the wrong comparison used in the Derivation.
If the Empno Field is of Datatype "Integer" then the Transformer derivation should be compared to "0"
if dslnk.left.empno=0 then dslnk.right.empno else dslnk.left.empno
if the Datatype is Varchar you can use the below Derivation should be compared to ''(empty). In Most of the cases you will get '' not NULL as the value might be defaulted to Column default value.
if nulltoempty(dslnk.left.empno)='' then dslnk.right.empno else dslnk.left.empno
If the Empno Field is of Datatype "Integer" then the Transformer derivation should be compared to "0"
if dslnk.left.empno=0 then dslnk.right.empno else dslnk.left.empno
if the Datatype is Varchar you can use the below Derivation should be compared to ''(empty). In Most of the cases you will get '' not NULL as the value might be defaulted to Column default value.
if nulltoempty(dslnk.left.empno)='' then dslnk.right.empno else dslnk.left.empno
Harish