DSXchange

[quote="pavankvk"]

After the left outer join,u can check for null value in the non key columns of the right stream and then output the non-null stream to a funnel.----> 1

take the null stream to a aggregator again and this time, ur keys are ID,POS.
quote]

I am lost here:

Left join on ID and how to check null values.

Thanks

i explained in the post above..

if still unclear mail me at bitnbyte @ gmail

i can send u the job export in ver 7.5

instead of join u can use a change cpature stage..but still i will explain join

in join stage if u specify lef outer then

for the unmatched records, value will be null for columns originating from the right..

better email me..i can send u the export.

Ok, here's another approach to do it with stage variables.

First, sort the data in ascending order of ID and POS. In transformer, use 5 stage variables in the given order: NowID, NowPOS, rej, thenID, thenPOS:
NowID=ID
NowPOS=POS
If NowID=thenID
then if nowID:nowPOS=thenID:thenPOS
then rej='F'
else rej='T'
else rej='F'
thenID=ID (give a default of -1)
thenPOS=POS (give a default of -A)

Now pass all the required columns along with "rej" stage variable to the next stage, which is sort stage. Here, sort on ascending order of ID and descending order of "rej". THen use another transformer with following stage variables...again, the order is important:
nowID=ID
nowstatus=rej
If nowID=thenID
then
nowstatus=thenstatus
else
nowstatus=rej
thenstatus=nowstatus
thenID=ID (give a default of -1)

Finally, in constaints, pass all rej='T' s to the reject dataset and pass all rej='F' s to further processing that you may have.

Letme know if you have any questions.

Just add a reject in your transfer for POS= C and POS=D or whatever you want to reject. Or have the rejects or the filters in a file or a Dataset that you can lookup to. Or you can use change capture stage. Or a Join Stage.
Too many to state