January 23, 2012 at 3:23 pm
Hi
I am having two tables :
Table 1
ID NAME S-CODE
10 XYZ 1
20 ABC 2
Table 2:
ID TEST INDICATOR
10 QA N
10 BP Y
20 SC N
20 UT Y
I am using a merge transformation and loading the data in the flat file.
The final file looks exacltly what i was looking for (i am sorting on id before merge)
10 XYZ 1
10 QA N
10 BP Y
20 ABC 2
20 SC N
20 UT Y
But now i have a requirement to create flat file. When the S-code is 1, then from the Table 2, only those records should be loaded in the destination which has Indicator = 'Y'. So i am expecting the file should look like:
10 XYZ 1
10 BP Y
20 ABC 2
20 SC N
.20 UT Y
So the record (10 QA N) should not be loaded because it has corresponding 'N' in INDICATOR column in table 2.
Please suggest how to proceed to create such a file with above mentioned requirement.
Thanks
January 24, 2012 at 5:43 am
If you use the MERGE JOIN, you keep the columns of both tables.
Then you can easily test your condition in a conditional split:
(s-code == 1) && (indicator == N)
Don't assign the output of this condition and you got rid of those rows.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 24, 2012 at 10:04 am
Actually i am using merge and i designed the whole package. I just want to add this condition.
Can i still use your suggestion if i continue to use merge instead of merge join?
January 24, 2012 at 1:00 pm
EDIT ***
Sorry, misread your post.
The Merge transform requires both sets of data to have the same columns. So no, you can't do what you're trying to do with the Merge transform.
EX:
Table 1
A B C
A B C
Table 2
A 2 3
A 2 3
Merge transform will result in
Table
A B C
A B C
A 2 3
A 2 3
Merge join transform will result in
Table
A B C 2 3
A B C 2 3
You'll need to use the Merge Join transform to do what you're trying.
Or, if you know the rows will always be a 1-1 type match, you could use the Lookup transform. It's better, but it doesn't work if there's multiple rows returned for each lookup.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply