DSXchange

Hi All,

I am trying to load the Target table as I have one column in the source which is a combination of Numeric and alphabetical data i.e letters and numbers but i wanted to load in the target as letters should load into letter column and the numbers would go into number column

I have tried alpha function but still i am getting warnings saying that non numeric data is entered when numeric required.

As my source column also has B- ,B+, A - and so on

How would I handle this

Please provide your valuable inputs.

Thanks

The warning indiactes that you are forcing a non-numeric data into a numeric field. What is the DataType for those two columns and what functions you are using for tranformations?

For numbers I would remove the plus "+" sign and any spaces. I've never tried loading a trailing negative sign so you might have to detect it, remove it then use the NEG() function (or move it to the front of the number).

Since you start with a string, there is always the possibility of non-printable characters (carriage return, tabs, linefeeds, etc.) that won't convert to numeric. You'd have to remove those too.

I would suggest adding a reject file on your job so you can look at the data that doesn't get loaded.

John

For Alpha:
Convert('<Enter all other Non-Alpha characters> ','',FieldName)

For Non-Alpha :
Convert('<Enter all Alpha Characters>','',FieldName)


Thanks,
Madhu

Use DIGITS() to extract all numbers from alpha-numeric data and LETTERS() or ALPHA() one of those I believe (cant remember exactly) for alphabets.

hi dsguru2b ,

there is no digits function in the i have checked please tell me syntax of that function

thanks

i have datatypes as letter column as varchar and the number column as decimal . but in the source it is varchar

Its a transform functioin. Its syntax is exactly what I gave you. where in.Col is your incoming column.
You can also use OCONV with MCN.

Hi DSguru,


I have tried by using LETTERS and DIGITS transform functions , but here is my doubt i have apart from alphabetic and numeric fields coming from the source but also the source table has something like A+ ,A- ,B+ B- but these are not loading into the target .

What could be done to capture the letters having +,- atlast

Any input would be appreciated


Thanks

If thats the case then use EREPLACE() or convert() to replace all alphabets for so called numeric fields and replace all numbers for strings that dont require numbers.

I did not get your point please elaborate it .

what exactly you want me to do ? I am fine with the letters an ddigits functions inspite of that wht else i need to add
to the transformer stage


Thanks

Ok now your confusing me, I thought that you wanted to capture other characters like (-) and (+) etc. Isnt that the case?
Once again, for only extracting numerics use
for only alphabets use If you want everything except numerics then use This is what will get you A- and A+

And at last, if you need everything except alphabets then use Forget the EREPLACE() or CONVERT() functions.

Hi DSguru,

I am not confusing you just let me be clear with this

I have a source column which is a combination of A,A+,B,B-,A- , C,D,E,S,U, 0,1,2,3,5,10,20,25,30,99,100 and I have two columns in the target which are letter and Number , the letters from the source should go into the letter column and the numbers alltogether into the Number column . But for the letters its not only A,B,C,D,E,U,S the target should also have A+,B-,A-,B+ etc . for that I am using the functions mentioned by you . when i ran the job and view the data i could not able to see the A+,A-,B-,B+ etc., how do i capture this in the target column .

I think i am clear now .

Please suggest me the valuable inputs.

Thanks

Use the above for alphabet column. This will give you A-, B+ etc.
Use the above for numeric field. This will give you only numbers.