Merging Records

DSguru2B · Post by **DSguru2B** » Tue Mar 20, 2007 7:49 am

Thats even more confusing now. Where is File B's data. Use 'code' tags to maintain formatting.

chulett · Post by **chulett** » Tue Mar 20, 2007 7:50 am

Something like this? Can we get an 'exactly like this' sample?

pravin1581 · Post by **pravin1581** » Tue Mar 20, 2007 8:02 am

chulett wrote:Something like this? Can we get an 'exactly like this' sample?

Sorry it is exactly like this . File A and File B are the 2 different input files and File C is the output file.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 20, 2007 5:46 pm

Here's what I meant by add a line number.

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

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 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

You can also see what we mean by enclosing your example in Code tags.

pravin1581 · Post by **pravin1581** » Wed Mar 21, 2007 12:32 am

ray.wurlod wrote:Here's what I meant by add a line number.
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 
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 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 
You can also see what we mean by enclosing your example in Code tags.

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.

pravin1581 · Post by **pravin1581** » Wed Mar 21, 2007 1:33 am

chulett wrote:
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.
This doesn't help at all to explain your requirements.

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.

It should be 20.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 21, 2007 5:55 am

OUTER join. It will work.

chulett · Post by **chulett** » Wed Mar 21, 2007 7:00 am

Yup, just make sure you OUTER in the right direction.

ps. That's not a 'cartesian product'.

DSguru2B · Post by **DSguru2B** » Wed Mar 21, 2007 7:14 am

If you need unmatching records from both the files then do a FULL OUTER JOIN.

pravin1581 · Post by **pravin1581** » Wed Mar 21, 2007 10:41 pm

DSguru2B wrote:If you need unmatching records from both the files then do a FULL OUTER JOIN.

None of the join conditions in the Merge stage is giving the desired result.

chulett · Post by **chulett** » Wed Mar 21, 2007 11:13 pm

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.

pravin1581 · Post by **pravin1581** » Wed Mar 21, 2007 11:47 pm

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.

[/code]

File A File B
-------- --------
UPC ORG STORE DATE
01 ALB 001 01/21/2007
02 ALB 002 01/22/2007
03 ALB 003 01/23/2007
01/24/2007
01/25/2007

File C
--------

UPC ORG STORE DATE
01 ALB 001 01/21/2007
01 ALB 001 01/22/2007
01 ALB 001 01/23/2007
01 ALB 001 01/24/2007
01 ALB 001 01/25/2007
02 ALB 002 01/21/2007
02 ALB 002 01/22/2007
02 ALB 002 01/23/2007
02 ALB 002 01/24/2007
02 ALB 002 01/25/2007
03 ALB 003 01/21/2007
03 ALB 003 01/22/2007
03 ALB 003 01/23/2007
03 ALB 003 01/24/2007
03 ALB 003 01/25/2007

The File A and File B are the input files and the File C is the output file with the desired output.

chulett · Post by **chulett** » Thu Mar 22, 2007 12:50 am

[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.

pravin1581 · Post by **pravin1581** » Thu Mar 22, 2007 3:38 am

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.

The line number has been added to both the files and we are joining on the basis of line number as key. The join condition is Complete Set. But the output fie is not coming in the desired format. The files are getting appended . The date field is blank besides the filelds of File A .ie. UPC, ORG, Store and Date field gets appended below the Fileds of File A.

chulett · Post by **chulett** » Thu Mar 22, 2007 7:05 am

Now it just sounds like you haven't set the stage up properly.

Perhaps it's time to call Support and let them work with you on the last bits? It gets difficult to analyze things like this without access to the actual job(s) in question.