Posted: Tue Mar 20, 2007 7:49 am
Thats even more confusing now. Where is File B's data. Use 'code' tags to maintain formatting.
Sorry it is exactly like this . File A and File B are the 2 different input files and File C is the output file.chulett wrote:Something like this? Can we get an 'exactly like this' sample?
Code: Select all
File A File B
--------- ------------------
# UPC ORG # STORE DATE
1 01 ALB 1 001 01/24/2007
2 02 ALB 2 002 01/25/2007
3 03 ALB 3 003 01/26/2007
4 04 ALB 4 004 01/27/2007
5 05 ALB 5 005 01/28/2007
Code: Select all
File C
------------------------
UPC ORG STORE DATE
01 ALB 001 01/24/2007
02 ALB 002 01/25/2007
03 ALB 003 01/26/2007
04 ALB 004 01/27/2007
05 ALB 005 01/28/2007
But the output file should also contain the dates for which there are no values in the first file , i.e. the rows of both the files are not equal , I think the key functionality will not work here.ray.wurlod wrote:Here's what I meant by add a line number.Now you've got something on which to base the join, so that the output file contains the following fields and the corresponding values are :Code: Select all
File A File B --------- ------------------ # UPC ORG # STORE DATE 1 01 ALB 1 001 01/24/2007 2 02 ALB 2 002 01/25/2007 3 03 ALB 3 003 01/26/2007 4 04 ALB 4 004 01/27/2007 5 05 ALB 5 005 01/28/2007
You can also see what we mean by enclosing your example in Code tags.Code: Select all
File C ------------------------ UPC ORG STORE DATE 01 ALB 001 01/24/2007 02 ALB 002 01/25/2007 03 ALB 003 01/26/2007 04 ALB 004 01/27/2007 05 ALB 005 01/28/2007
It should be 20.chulett wrote:This doesn't help at all to explain your requirements.pravin1581 wrote:I am unable to use the merge stage as it requires a key to be defined for joining , which is not present in my case . I require the SQL select A from B and C from D to generate the output file.![]()
At a high level, let's say your two files have record counts like this:
FileA: 2 records
FileB: 10 records
How many records are you expecting in your output? 2? 10? 12? 200? All are valid answers and require different solutions to implement.
None of the join conditions in the Merge stage is giving the desired result.DSguru2B wrote:If you need unmatching records from both the files then do a FULL OUTER JOIN.
chulett wrote:![]()
Let's play the 'please give us some sample data' game again. This time, using code tags much like Ray did, can you supply a small set of your actual input data and what the desired output should look like? Three separate examples would be best, not side by side so they don't all run together like last time.
And please be sure the 'desired output' sample is accurate. Last time it didn't make much sense, probably because the lack of the code tags caused it to 'left justify' everything. Perhaps show the fields with commas rather than spaces between them this time so we know when you are trying to show empty fields, as in the result of an OUTER join.
Then maybe we'll have a clue what kind of join / merge you need.
chulett wrote:[sigh]
You missed the code tags. Please use 'Preview' before you post something to check that it will be ok. And we still can't tell which columns are from 'File A' and which are from 'File B'... why do you insist on putting them side by side?![]()
Squinting into my crystal ball, it looks like Ray's response way long time gone is your answer and probably why he's been quiet and letting others continue to thrash around masochistically.
Preprocess each file and add a line number to serve as the 'key' for the Merge stage. Choose Complete Set as the Join Type. Done.