Route one of Duplicates based on condition
Moderators: chulett, rschirm, roy
Route one of Duplicates based on condition
I am having DTS issue where i need to Route one of Duplicate value to different file and then remove duplicates. Here is an examples of how data looks for columns Code & Status
Case 1
Code Status
1DKSSK2 Removed
1DKSSK2 Removed
1DKSSK2 Removed
Case 2
Code Status
1DKSSK1 Implemented
1DKSSK1 Removed
1DKSSK1 Removed
Case 1 - I have to fetch first row with Status =Removed to different file and eliminate other two rows
Case 2 - Fetch first row with Status=Implemented to different file and eliminate other rows
Sorted data on Code/Status and can i use SWITCH stage to Extract first row and then 'Filter Stage' to Eliminate rows where Status <> Removed.
Will this work ? Any better way to do it
Thanks in advance
Sarathi
Case 1
Code Status
1DKSSK2 Removed
1DKSSK2 Removed
1DKSSK2 Removed
Case 2
Code Status
1DKSSK1 Implemented
1DKSSK1 Removed
1DKSSK1 Removed
Case 1 - I have to fetch first row with Status =Removed to different file and eliminate other two rows
Case 2 - Fetch first row with Status=Implemented to different file and eliminate other rows
Sorted data on Code/Status and can i use SWITCH stage to Extract first row and then 'Filter Stage' to Eliminate rows where Status <> Removed.
Will this work ? Any better way to do it
Thanks in advance
Sarathi
Have a lookup on the first set where STATUS='Removed' with the second set, if you find any record then PASS this records to an Sequential Stage which would be your STATUS=IMPLEMENTED records and wherever you won't find PASS them to a Reject Link which would be your STATUS=REMOVED records
Transformer -> Removed ->Rem_dup
->Implemented ->Rem_dup ->Lkp ->IMPLEMENTED
|
REMOVED
Transformer -> Removed ->Rem_dup
->Implemented ->Rem_dup ->Lkp ->IMPLEMENTED
|
REMOVED
Thanks for reply. I have only one query which pulls data and you mentioned to go for lookup below. Does it mean LOOKUP Stage ?
As i don't have Two Sources prefer Transformer Stage.
- If Rem_status='Removed' and rem_status=imp_status Then Pass this record to seq stage else Reject record.
Let me know above logic is corrected.
Have a lookup on the first set where STATUS='Removed' with the second set, if you find any record then PASS this records to an Sequential Stage which would be your STATUS=IMPLEMENTED records and wherever you won't find PASS them to a Reject Link which would be your STATUS=REMOVED records
As i don't have Two Sources prefer Transformer Stage.
- If Rem_status='Removed' and rem_status=imp_status Then Pass this record to seq stage else Reject record.
Let me know above logic is corrected.
Have a lookup on the first set where STATUS='Removed' with the second set, if you find any record then PASS this records to an Sequential Stage which would be your STATUS=IMPLEMENTED records and wherever you won't find PASS them to a Reject Link which would be your STATUS=REMOVED records
-
- Participant
- Posts: 57
- Joined: Wed Oct 21, 2009 4:46 am
- Location: India
-
- Participant
- Posts: 29
- Joined: Wed Jan 06, 2010 7:18 am
Below is method i followed and did not work. Am i making any mistakes here ?
1- Sorted data on Key and Status
2- In the transformer created separate two types of records using constraint
1) Status = Removed
2) Status = Implemented
3 - Duplicate state and pulled only Last record
4. Created STAGE VARIABLE in NEXT Transformer Stage
If (Impl_STATUS <> Rem_STATUS) Then Impl_STATUS
and linked to Impl_Status
5. Linked one output to Sequential and another to my old Lookup
Sarathi
1- Sorted data on Key and Status
2- In the transformer created separate two types of records using constraint
1) Status = Removed
2) Status = Implemented
3 - Duplicate state and pulled only Last record
4. Created STAGE VARIABLE in NEXT Transformer Stage
If (Impl_STATUS <> Rem_STATUS) Then Impl_STATUS
and linked to Impl_Status
5. Linked one output to Sequential and another to my old Lookup
Sarathi
Are these the only possible values of STATUS?
If so use a sort stage (on CODE) and generate a key change column. The data must be partitioned on CODE.
You will need two output links with constraints to meet your described logic.
The keyChange column has a value of 1 for the first occurence of a code, then the rest are 0.
Constraint 1: keyChange = 1 AND STATUS = "Removed"
Constraint 2: keyChange= 1 AND STATUS = "Implemented"
If so use a sort stage (on CODE) and generate a key change column. The data must be partitioned on CODE.
You will need two output links with constraints to meet your described logic.
The keyChange column has a value of 1 for the first occurence of a code, then the rest are 0.
Constraint 1: keyChange = 1 AND STATUS = "Removed"
Constraint 2: keyChange= 1 AND STATUS = "Implemented"