Aggregrator Problems

[kpmINFO]

Hi I need to know in my mapping i have to do a specific validation for incoming flat file.

Flat file structure is as follows
col1 col2 db cr
1 2 10.00 0.00
2 5 0.00 20.00
3 4 10.00 0.00
I need to check the sum of Db and Sum of CR , and send a corresponding Err T or False to the next transformation , I m looking into expression basically , please let me know how as to i can return from active trfn to passive trfn , isthere a better solution to this

Thnaks
PM

 

[blom0344]

Would you mind using plain english for readability's sake? (I mean as opposed to the phonetic abbreviations)

Do you intend to load any data to a target? Or do you just want to validate that gross totals for credit/debet are the same?

If you want to load data based on a validation, I would once again suggest a logical entity that holds either false or true and use this with a lookup to load or not load data...

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Dagon]

If the summing up to zero needs to be done across the entire input file (i.e. all transactions in the file add up to zero), then I suggest you just create a new source qualifier-target pipeline within your mapping which validates the file before running the part of the mapping which loads it. You could simply sum up the debit/credit columns using the aggregator and then call abort() to terminate the mapping if it doesn't equal zero.

If the summing up is at the level of some some of key (e.g. all transactions within each grouping of col1 and col2 add up to zero), you could use an expression transformation to keep a running total. Sort the input by the key columns and use variables in the expression transformation to store the values of the current key(s) and the running total for that key. Whenever the key changes, validate that the total of the previous key is zero. You would have to append some sort of terminator record to avoid missing the validation on the last key.

If one of the keys doesn't add up, you could either fail the whole mapping or use a transaction control to rollback everything associated with that key but carry on loading the others.