[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
stage variables
Moderators: chulett, rschirm, roy
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.
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![Laughing :lol:](./images/smilies/icon_lol.gif)
Too many to state
![Laughing :lol:](./images/smilies/icon_lol.gif)
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.