Handling rejected rows

[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

[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.

[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.

[parag.s.27]

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.

[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

Code: Select all

Link Collector1----->TX----->Tgt

Hope this helps.

[parag.s.27]

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.

[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

[parag.s.27]

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.

[parag.s.27]

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.

[kumar_s]

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.

[parag.s.27]

this is the job[/url]

[kumar_s]

this is the job[/url]

I guess you may need to edit this post.

[ameyvaidya]

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.