PX Join Stage - Full Outer

[Salegueule]

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

[sun rays]

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

Can you please explain what do you mean by "both the output file"

[vmcburney]

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.

[track_star]

You might want to consider the merge operator....