Hi,
I have a scenario in my project, in a column there are two values separated by comma as shown below.
Input
-----------
AUS,IND
AUS,PAK
IND,AUS
IND,PAK
PAK,AUS
PAK,IND
If you observe in the above data, there a combinations of "AUS,IND" and "IND,AUS". In this case I have to consider them as duplicates and load any one of them in table.
My output should look as below.
Expected Result
--------------------
AUS,IND
AUS,PAK
IND,PAK
PAK,AUS
Please help me with this scenario.
Many Thanks in advance.
Scenario: Identify duplicated in a column
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 15
- Joined: Fri Jan 22, 2010 4:35 am
- Location: India
-
- Participant
- Posts: 527
- Joined: Thu Apr 19, 2007 1:25 am
- Location: Melbourne
-
- Participant
- Posts: 15
- Joined: Fri Jan 22, 2010 4:35 am
- Location: India
Simple, but works as required.
Transformer:
COL1 --> is your input with two fields separated by ','
Declare Below Stage Variables:
svIndex - Index(DSLink1.COL1,',',1) This will find the position of ','
svLength - Len(DSLink1.COL1) this is to find the length of your column
svField1 - Left(DSLink1.COL1,svIndex-1) Left portion of your input column
svField2 - Right(DSLink1.COL1,svLength-svIndex) Right portion of your input
Declare an output column:
MERGEKEY -
If Compare(svField1,svField2) > 0 Then
svField1:svField2
Else
svField2:svField1
Finally Have a Sort Stage - to remove duplicates on MERGEKEY
Let me know if it solves your requirement.
Transformer:
COL1 --> is your input with two fields separated by ','
Declare Below Stage Variables:
svIndex - Index(DSLink1.COL1,',',1) This will find the position of ','
svLength - Len(DSLink1.COL1) this is to find the length of your column
svField1 - Left(DSLink1.COL1,svIndex-1) Left portion of your input column
svField2 - Right(DSLink1.COL1,svLength-svIndex) Right portion of your input
Declare an output column:
MERGEKEY -
If Compare(svField1,svField2) > 0 Then
svField1:svField2
Else
svField2:svField1
Finally Have a Sort Stage - to remove duplicates on MERGEKEY
Let me know if it solves your requirement.
-
- Participant
- Posts: 15
- Joined: Fri Jan 22, 2010 4:35 am
- Location: India