How to get unmatched record from Outer join in PX

[cyh]

I have defined a Join stage using fuller outer join. And I have explicitly copied the key from both input into the output dataset.

Image that I have pass the output to a Transformer stage, and set some criteria to capture the unmatched records from either side.

When I start looking for unmatched records in the output, I just can't get them. I have tried following methods :

1) IsNull(Link1.Key)
2) Len(Link1.Key) = 0
3) RawLength(Link1.Key) = 0

I have checked that the output with Data Set Management, and the key has nothing within it, and it is not null.

Would any one please help by suggesting
1) a best way to look for unmatched records of two inputs.
2) how to set the criteria so that I can capture those records.

Thanks in advance.

[santhu]

[quote="santhu"]

Hi,

First of all, when you use JOIN stage, you cannot capture any unmatched data in the output link of the JOIN stage. So any condition under Transformer will not help.


There are 3 ways of Horizontally combining data, i.e JOINS, LOOKUP and MERGE.

Possibilites of capturing unmatched data for

1) JOIN: You cannot capture unmatched data for any kind of join using the JOIN stage i.e neither from the left nor the Right inputs.

2) LOOKUP: Lookup stage has only 1 Primary Source and can have N lookups / secondary data/reference data. You can capture unmatched primary data in the Reject set (1 only) if you specify "Reject" option in the lookup stage settings. You cannot capture unmatched secondary / lookup data

3) MERGE: This stage has 1 MASTER source and can have N update / secondary sources. You can KEEP / DROP the master source data if not matching, and you can capture all the N unmatching update / secondary sources into respective N Reject files.

Hope this helps to solve your issue

Regards,
Santhosh S

[ariear]

Yes that's the way it work with a PX transformer. It seems to work O.K. with a Basic transformer if you're willing to take it as an alternative.
The truth is that I haven't figured out yet how PX deals with NULL values it seems to have many strange issues with it.

[cyh]

Thanks to santhu.

You stated that there is no possible to capture the unmatch record from JOIN. However, when I check the output form an full outer join, the recrods are there !!! I just wonder how to get it out ..... Any tips ?

The output looks like this :

Code: Select all

L Key    R Key      L Value     R Value
A          A            V1            V9
A          (?)          V2            V9
(?)        A            V2            V9

(?) indicator nothing there, but not a null value ....

Furthermore, DataStage support staff had once warned us that using LOOKUP for smaller lookup table ONLY .....

For MERGE, can I identify all the unmatch cases in 1 pass .... i.e. Records found in left only or those found in right only ....

[sandy]

Its true that, we cannot capture the unmatched records in a JOIN stage, but we can always do that by using a Transformer stage following the JOIN stage.

IsNull(leftKey) - by using this we can figure out if there was an unmatched record in the left link.

leftKey="" - should work if its a VARCHAR type.

similarly unmatched records on the right link can be captured using "rightkey".

-sandyla
brickbats welcome.

[cyh]

Finally, I got the answer....

1) Put a transfomer about the join stage
2) Create stage variable to work out if the key is null or not. Refer to the sample code shown below.
3) Create output link from transformer and setup the constraint accordingly to the stage variable defined.


Sample code used in stage variable :

Code: Select all

If Joined_Smry_Write.rightRec_RCLE_KEY =  Str(Char(0), 20) Then 'Y'  Else 'N'

Sample code used in constraint :

Code: Select all

GLEAMNull = 'Y' and RcleNull = 'N'

HTH

[pavankvk]

Hi there

IsNull function will not work for a int data type.So look there