hash file lookup fails

[scottr]

i have flat file as source with part_no as varchar(8) and hash file(as lookup) with part_no as varchar(8).but some of the part_no's are 6 digits and 7 digits.out of these valuse some has leading spaces.
and also some of part_no's with 8 digits has leading zeros. are these values causing the lookup fails??
also how to trim those 6,7 digit values which has leading spaces.

thanks in advance...

Scott R

[scottr]

sorry,. in the above post the varchar value in source file and lookup file is 8

[vmcburney]

I would put this code into a routine so you can use the Test button to test different scenarios and so you can add and refine the code over time without having to change or recompile the jobs that use it.

The simplest routine code would be:
Ans = FMT(Trim(Arg1), "Z")

The trim statement will remove leading or trailing spaces, the FMT statement is the all singing all dancing format command for BASIC, the "Z" format option is a numeric formatting option that removes leading zeros.

Some code you might like to add is a check to see if the field is NULL, or if it is all zeros (the FMT will change it to an empty field), or if it has non numerics in it, etc.

See the server job developers guide for a full description of the format codes and string handling commands.

In your transformer when you join the input field to the lookup key field just add the routine to the Key Expression field. This will ensure the formatted value is used for a lookup instead of the raw input value.

[jayawant_hsbc]

may be u can use trim (ur fieldname' ' 'A')
iam not sure about the syntax just check on that a stands for trim all spaces include space inbetween single quotes