Page 1 of 1
Handling rejected rows
Posted: Mon Mar 06, 2006 3:55 am
by parag.s.27
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
Posted: Mon Mar 06, 2006 4:10 am
by ArndW
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.
Posted: Mon Mar 06, 2006 4:14 am
by loveojha2
Then you would need to change your design of job from
Code: Select all
Hashed File
|
|
|
V
Src----->TX----->TGT
To
Code: Select all
Src
|
|
|
V
Hashed File----->TX----->TGT
|
|
|
V
Reject Link
Hope this helps.
Posted: Mon Mar 06, 2006 4:19 am
by parag.s.27
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.
Hi ArndW,
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.
Posted: Mon Mar 06, 2006 4:53 am
by loveojha2
How about using link collector for this.
Code: Select all
Hashed File
|
|
|
V
Src----->TX----->To Link Collector1
Src
|
|
|
V
Hashed File----->TX----->To Link Collector1
|
|
|
V
Reject Link
and after that
Hope this helps.
Posted: Mon Mar 06, 2006 5:05 am
by parag.s.27
loveojha2 wrote:Then you would need to change your design of job from
Code: Select all
Hashed File
|
|
|
V
Src----->TX----->TGT
To
Code: Select all
Src
|
|
|
V
Hashed File----->TX----->TGT
|
|
|
V
Reject Link
Hope this helps.
Hi,
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.
Posted: Mon Mar 06, 2006 5:05 am
by ameyvaidya
Try This:
Code: Select all
JanOnlyHashedFile
|
|
|
|[RefLink]
|
Jan&FebSrcFile--------> Transformer------------------------------------>Output1
|
|[Only Feb Constraint: IsNull(<RefLink>.<AnyColumn>)]
|-------------------------------------------->Output2
Output 1 Will have All Account Numbers.
Output 2 will have all the Feb Only Account Numbers.
IHTH
Posted: Mon Mar 06, 2006 9:22 am
by parag.s.27
ameyvaidya wrote:Try This:
Code: Select all
JanOnlyHashedFile
|
|
|
|[RefLink]
|
Jan&FebSrcFile--------> Transformer------------------------------------>Output1
|
|[Only Feb Constraint: IsNull(<RefLink>.<AnyColumn>)]
|-------------------------------------------->Output2
Output 1 Will have All Account Numbers.
Output 2 will have all the Feb Only Account Numbers.
IHTH
Hi,
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.
Posted: Mon Mar 06, 2006 9:24 am
by parag.s.27
ameyvaidya wrote:Try This:
Code: Select all
JanOnlyHashedFile
|
|
|
|[RefLink]
|
Jan&FebSrcFile--------> Transformer------------------------------------>Output1
|
|[Only Feb Constraint: IsNull(<RefLink>.<AnyColumn>)]
|-------------------------------------------->Output2
Output 1 Will have All Account Numbers.
Output 2 will have all the Feb Only Account Numbers.
IHTH
Hi,
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
Thus the problem is i want to catch missing 6 records.
Posted: Mon Mar 06, 2006 9:47 am
by kumar_s
parag.s.27 wrote:ameyvaidya wrote:Try This:
Code: Select all
JanOnlyHashedFile
|
|
|
|[RefLink]
|
Jan&FebSrcFile--------> Transformer------------------------------------>Output1
|
|[Only Feb Constraint: IsNull(<RefLink>.<AnyColumn>)]
|-------------------------------------------->Output2
Output 1 Will have All Account Numbers.
Output 2 will have all the Feb Only Account Numbers.
IHTH
Hi,
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
Thus the problem is i want to catch missing 6 records.
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. 
Posted: Mon Mar 06, 2006 10:46 am
by parag.s.27
this is the job[/url]
Posted: Mon Mar 06, 2006 11:46 am
by kumar_s
parag.s.27 wrote:this is the job[/url]
I guess you may need to edit this post.
Posted: Mon Mar 06, 2006 11:53 pm
by ameyvaidya
kumar_s wrote:
Yes it is often difficult to get these in right position.
Good Ol' Notepad (Font:Courier) is my preferred Canvas. The rest is just copy and Paste.