Hi,
I need to merge 2 files together that have the same composite key structure. I am using a Join stage with the Full Outer join option.
File 1:
Column Key (y/n)
--------- -----------
Report_date y
Level_1 y
Level_2 y
Level_3 y
WorkVolume n
File 2:
Column Key (y/n)
--------- -----------
Report_date y
Level_1 y
Level_2 y
Level_3 y
WorkVolume n
Sum_VarCounter n
Sum_TotalInterval n
Desired Output file:
Column Key (y/n)
--------- -----------
Report_date y
Level_1 y
Level_2 y
Level_3 y
WorkVolume n
Sum_VarCounter n
Sum_TotalInterval n
Works well when the key instance only exist in only one of the files but when the key instance is present in both the ouput file...
I could handle the key occurence in a Transform stage and check for a specific key value of 0 on one of the of the key column group and passed the correct key group to the output file:
if Join_FcstDly.Level_1_Id = 0 then Join_FcstDly.rightRec_Level_1_Id else Join_FcstDly.Level_1_Id
but not sure that it is the best to handle this situation. Any suggestions?
Thanks
PX Join Stage - Full Outer
Moderators: chulett, rschirm, roy
Re: PX Join Stage - Full Outer
Can you please explain what do you mean by "both the output file"Salegueule wrote:Hi,
I need to merge 2 files together that have the same composite key structure. I am using a Join stage with the Full Outer join option.
Works well when the key instance only exist in only one of the files but when the key instance is present in both the ouput file...
Thanks
Last edited by sun rays on Thu Oct 13, 2005 11:22 am, edited 1 time in total.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
Is File 2 a subset of File 1? IE. Can you process file 1 and perform a left outer join/lookup against file 2? You do not seem to be aggregating anything from File 1 so a full outer join seems like an overly complex way to merge them. Try reading in both files and funnelling them together, then send them to an aggregation stage.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 60
- Joined: Sat Jan 24, 2004 12:52 pm
- Location: Mount Carmel, IL