How to remove additional zeros in a record using Transformer

ssunda6 · Post by **ssunda6** » Mon Sep 25, 2006 12:38 am

Hi all,
i want to remove additional zeros from records using a transformer. I have tried with Trim function but it is not working .Also, please give the format in which this has to be done.

Kirtikumar · Post by **Kirtikumar** » Mon Sep 25, 2006 12:58 am

Whats your datatype for which you are trying to remove leading zeros?

If it decimal, then after removal, you will have to carry it as char or varchar or int. Because, in PX decimal are always have leading zeros when they are stored in datasets or carried through links (i.e. virtual datasets).

If your input is decimal and then use DecimalToString(InCol, 'supress zero')

If the in col is string, then a normal trim should work.

May be some special handling for first char of the col, will be needed as it will be sign char.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 25, 2006 1:11 am

How precisely have you specified your Trim() function?

keshav0307 · Post by **keshav0307** » Mon Sep 25, 2006 3:32 am

Please give more details about the problem, like what is the input datatype, what is output datatype etc. Because there are many ways you can handle it.

ssunda6 · Post by **ssunda6** » Tue Sep 26, 2006 11:15 pm

ray.wurlod wrote:How precisely have you specified your Trim() function? ...

Hi,
I have tried with Trim function Trim("columnname","0","L") but this is not working.The data type is Char in input and output.Let me know if there is any other format which i need to give.With the above function used I am getting junked data in the output.Also i have tried various other formats of trim.

Lucky · Post by **Lucky** » Tue Sep 26, 2006 11:27 pm

Hi,

As far as I know Trim function works for Varchar data type. You cannot use Trim for fields which are of Char data type.

Please correct me if I am wrong.

Kirtikumar · Post by **Kirtikumar** » Wed Sep 27, 2006 12:07 am

As I am from server bkg, my understaning is it works for all.

I creates a job with char (5) having leading zeros and then tried trim on it.

It is giving proper results i.e. 00005 is converted to "5 " value after using trim.

So I feel there is no such restriction.

ArndW · Post by **ArndW** » Wed Sep 27, 2006 1:23 am

Lucky wrote:...As far as I know Trim function works for Varchar data type. You cannot use Trim for fields which are of Char data type...

You can use the TRIM() function on CHAR, but since the resultant output if of fixed length it will automatically pad the output according to string rules. You can use it to replace/remove zeroes.,

pankajg · Post by **pankajg** » Wed Sep 27, 2006 1:26 am

Using the trim function as you have mentioned
Trim("columnname","0","L")
would only remove the leading zeros from the "columnname" specified.

You need to be clear if you would want to remove leading 0 or trailing 0 or anything in betn as well..

Using option "A" Removes all occurrences of character
"B" Removes leading and trailing occurrences of character.
"T" Removes trailing occurrences of character.

Hope this helps

ray.wurlod · Post by **ray.wurlod** » Wed Sep 27, 2006 1:27 am

The OP has begun a new thread here on this topic.

michaeld · Post by **michaeld** » Fri Mar 16, 2007 11:12 am

This will do it:

DecimalToString(ColumnName),"fix_zero,suppress_zero")

NOTE: One of the previous posters spelled suppress with one p.

AaronVG · Post by **AaronVG** » Fri Mar 16, 2007 2:22 pm

Lucky wrote:Hi,

As far as I know Trim function works for Varchar data type. You cannot use Trim for fields which are of Char data type.

Please correct me if I am wrong.

I am not sure, but I will say that I was experiencing a challenge with a Lookup Stage as my input was Char and lookup reference was VarChar. Once I converted Char to VarChar (Transform Stage) it worked.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 16, 2007 6:12 pm

With a Modify stage it might work even faster. You will still need a string_trim function, however. Refer to Orchestrate Operators guide for more information, as they seem to have left this function out of the Parallel Job Developer's Guide. This document may also prove useful.