Hi Below is my input:
id country
100 India
100 USA
100 UK
200 India
200 France
200 Japan
300 UK
300 USA
Requirement: Here id having 2 groups (100, 200)
If country column contains "India" then I need to reject entire group not a single record and capture into a file.
Output:
id country
300 UK
300 USA
Reject Records:
id Country
100 India
100 USA
100 UK
200 India
200 France
200 Japan
Please let me know how to achieve this.
need logic to achieve requirement
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
It can be as simple as bifurcating the records with India in one link and others in another link. Join those two back together and filtering out the matched record to reject file and unmatched records to output file.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 35
- Joined: Mon May 06, 2013 5:59 am
Re: need logic to achieve requirement
Hi
Do like this
SEQ----------TRN------------ Target 1
Target2
Target 3
1)in Transformer keep the logic in (constraint : Index(country,'India',1) for Target1 and in that constraint itself you will find otherwise checkbox tick it so that the record which wont match that condition will move to the Target2.
2)In Target3 keep logic in constraint as id = 300
hope this will slove your problem.
Do like this
SEQ----------TRN------------ Target 1
Target2
Target 3
1)in Transformer keep the logic in (constraint : Index(country,'India',1) for Target1 and in that constraint itself you will find otherwise checkbox tick it so that the record which wont match that condition will move to the Target2.
2)In Target3 keep logic in constraint as id = 300
hope this will slove your problem.
In v8.5 or above of DataStage, you can accomplish this with a sort and a 2-pass loop in a parallel transformer. In any version, you can accomplish this with a transformer-sort-transformer combination.
v8.5+:
Partition/Sort on id
In the Transformer:
Loop Iteration 1: Determine if India is present in the group, set a flag to indicate yes or no
Loop Iteration 2: If Yes, send records to Reject link, if No, send records to main output link
Any version:
Transformer 1: Create a new integer column. If Country=India set the value to 1 otherwise set to 0
Sort: Partition on ID, sort on ID and new column (descending)
Transformer 2: Keep up with key changes. If first record in the key has the new column value = 1, send all records in that group to Reject, otherwise send them to the main output
Regards,
v8.5+:
Partition/Sort on id
In the Transformer:
Loop Iteration 1: Determine if India is present in the group, set a flag to indicate yes or no
Loop Iteration 2: If Yes, send records to Reject link, if No, send records to main output link
Any version:
Transformer 1: Create a new integer column. If Country=India set the value to 1 otherwise set to 0
Sort: Partition on ID, sort on ID and new column (descending)
Transformer 2: Keep up with key changes. If first record in the key has the new column value = 1, send all records in that group to Reject, otherwise send them to the main output
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.