Handling rejected rows
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Handling rejected rows
Hi,
I am having a lookup for accounts having any kind of transaction for a particular month. i.e. the key field is account number.
Now the lookup hash file is having some extra records other then the matching account numbers of lookup.
I want to extract those extra records in another file.
I tried the reject row concept of transformer stage but it failed because it always take those records from the lookup file for which there is any matching record.
can anyone please help on this
I am having a lookup for accounts having any kind of transaction for a particular month. i.e. the key field is account number.
Now the lookup hash file is having some extra records other then the matching account numbers of lookup.
I want to extract those extra records in another file.
I tried the reject row concept of transformer stage but it failed because it always take those records from the lookup file for which there is any matching record.
can anyone please help on this
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
It sounds like you need to change your lookup hashed file into the main input stream and your current data stream into the reference stream to get the intersection and thus the complement of your two sets. Your current design will, as you've already noted, only get you the reference rows that match the key and not any extra records.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Then you would need to change your design of job from
To
Hope this helps.
Code: Select all
Hashed File
|
|
|
V
Src----->TX----->TGT
Code: Select all
Src
|
|
|
V
Hashed File----->TX----->TGT
|
|
|
V
Reject Link
Success consists of getting up just one more time than you fall.
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Hi ArndW,ArndW wrote:It sounds like you need to change your lookup hashed file into the main input stream and your current data stream into the reference stream to get the intersection and thus the complement of your two sets. Your current design will, as you've already noted, only get you the reference rows that match the key and not any extra records.
Avtually i am having a condition where i have to use one source file format having all the transaction details regarding the particular account numbers, in the month of jan 06. And i am having another source file with some account numbers from jan06 month and some new in Feb month.
Now i have to apply some transformations on those accounts those are present in both months. But those account numbers present only in Feb06 month and not in jan06 month must be recorded in another source file.
Can this be achieved.
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
How about using link collector for this.
and after that
Hope this helps.
Code: Select all
Hashed File
|
|
|
V
Src----->TX----->To Link Collector1
Src
|
|
|
V
Hashed File----->TX----->To Link Collector1
|
|
|
V
Reject Link
Code: Select all
Link Collector1----->TX----->Tgt
Success consists of getting up just one more time than you fall.
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Hi,loveojha2 wrote:Then you would need to change your design of job fromToCode: Select all
Hashed File | | | V Src----->TX----->TGT
Hope this helps.Code: Select all
Src | | | V Hashed File----->TX----->TGT | | | V Reject Link
I changed my design to this, but don't know why, the records going in lookup file are 151 but comming out of it are 550. Why transformer is doing this.
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
Try This:
Output 1 Will have All Account Numbers.
Output 2 will have all the Feb Only Account Numbers.
IHTH
Code: Select all
JanOnlyHashedFile
|
|
|
|[RefLink]
|
Jan&FebSrcFile--------> Transformer------------------------------------>Output1
|
|[Only Feb Constraint: IsNull(<RefLink>.<AnyColumn>)]
|-------------------------------------------->Output2
Output 2 will have all the Feb Only Account Numbers.
IHTH
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Hi,ameyvaidya wrote:Try This:Output 1 Will have All Account Numbers.Code: Select all
JanOnlyHashedFile | | | |[RefLink] | Jan&FebSrcFile--------> Transformer------------------------------------>Output1 | |[Only Feb Constraint: IsNull(<RefLink>.<AnyColumn>)] |-------------------------------------------->Output2
Output 2 will have all the Feb Only Account Numbers.
IHTH
I tried all possible transformations but still its failing.
i'll show u the exact job design
Src file----->Sorter
|
|
v
Transformer for inducing key----->File with induced key
|
|
v
Transformer
for splitting
src file with
all months data
to multiple individual
months data
| |
| |
v v
December Jan File
file (1470 recs) (157 recs)
| |
| |(157)
|(1470) v
| Hash file
v /
/
Transformer<_____/(151)
for
finding JAN data
and DEC data
having Dec amount
substracted from
Jan amount.
| |
| |
v v
Jan file Dec File
151 1470
Thus the problem is i want to catch missing 6 records.
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Hi,ameyvaidya wrote:Try This:Output 1 Will have All Account Numbers.Code: Select all
JanOnlyHashedFile | | | |[RefLink] | Jan&FebSrcFile--------> Transformer------------------------------------>Output1 | |[Only Feb Constraint: IsNull(<RefLink>.<AnyColumn>)] |-------------------------------------------->Output2
Output 2 will have all the Feb Only Account Numbers.
IHTH
I tried all possible transformations but still its failing.
i'll show u the exact job design
Code: Select all
Src file----->Sorter
|
|
v
Transformer for inducing key----->File with induced key
|
|
v
Transformer
for splitting
src file with
all months data
to multiple individual
months data
| |
| |
v v
December Jan File
file (1470 recs) (157 recs)
| |
| |(157)
|(1470) v
| Hash file
v /
/
Transformer<_____/(151)
for
finding JAN data
and DEC data
having Dec amount
substracted from
Jan amount.
| |
| |
v v
Jan file Dec File
151 1470
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Yes it is often difficult to get these in right position. If you have made any test jobs, try to take a screen shots and past here using Img options available. But make is small.parag.s.27 wrote:Hi,ameyvaidya wrote:Try This:Output 1 Will have All Account Numbers.Code: Select all
JanOnlyHashedFile | | | |[RefLink] | Jan&FebSrcFile--------> Transformer------------------------------------>Output1 | |[Only Feb Constraint: IsNull(<RefLink>.<AnyColumn>)] |-------------------------------------------->Output2
Output 2 will have all the Feb Only Account Numbers.
IHTH
I tried all possible transformations but still its failing.
i'll show u the exact job design
Thus the problem is i want to catch missing 6 records.Code: Select all
Src file----->Sorter | | v Transformer for inducing key----->File with induced key | | v Transformer for splitting src file with all months data to multiple individual months data | | | | v v December Jan File file (1470 recs) (157 recs) | | | |(157) |(1470) v | Hash file v / / Transformer<_____/(151) for finding JAN data and DEC data having Dec amount substracted from Jan amount. | | | | v v Jan file Dec File 151 1470
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
Good Ol' Notepad (Font:Courier) is my preferred Canvas. The rest is just copy and Paste.kumar_s wrote: Yes it is often difficult to get these in right position.
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>