Leading 0's

[DS_MJ]

Hi there I have a Numeric value in a job that I am trying to trim just the leading zeros for example:


Field1 [Numeric 17,2] = 000000000000000.00

The result I would like would be 0.00

Any ideas?

Thanks

[skrakesh]

Use Round Function: Round(field_name,2)

or

Use longint

[DS_MJ]

Use Round Function: Round(field_name,2)

or

Use longint

I want to remove the leading 0's.

[DSguru2B]

Check out this post
viewtopic.php?t=101418

[DS_MJ]

Check out this post
viewtopic.php?t=101418

Thanks DS_Guru:

I used the TRIM(COL_NAME,'0','L') removes the leading zeros but then it removes the 0's from the decimal place too.

Example: 00012345.00

Name SQL TYPE LENGTH SCALE
COL_NAME Num 15 2

when I apply TRIM(COL_NAME,'0','L')
I get 12345
I want 12345.00

Any idea...went thru the forum and people talk about Decimal to String format, floors and fab.

[bharathappriyan]

Hi,

Please use Rnd(inputcolumn name) function.

[ray.wurlod]

That generates a random number - not, I suspect, what the OP desires!

In server jobs you can simply perform arithmetic (for example add zero to the number) and it returns the shortest possible string. You could then format it to have two decimal places.

In parallel jobs, it is not quite so easy. You can convert the decimal number to a string, use the Field() function to get that part of the string to the left of the decimal place, trim non-significant leading zeroes from that, concatenate the decimal placeholder and its following two digits then convert the resultant string back to decimal.

View Data will, of course, always display the leading zeroes whether they are there or not; that's its way of reporting the precision and scale to you.

[panchusrao2656]

what happens if it has a value like 00000000.00, it might end up like .00 when we use Trim("00000000","0","L")

[ray.wurlod]

When you format it to two decimal places it regains its leading zero.

[DS_MJ]

When you format it to two decimal places it regains its leading zero. ...

Hello ray.wurlod:

Would really appreciate if you could please tell me how to do this in Parallel job. Cause unable to find anything for format under parjdev.pdf or advpx.pdf. The Basic.pdf mentions the fmt() function but am unable to apply it.

[DS_MJ]

Could anyone point out what is wrong with this:

(TRIM(FMT(COL_NAME,"R2"),'0','L')

Example: 00012345.00

Name SQL TYPE LENGTH SCALE
COL_NAME Num 15 2

when I apply TRIM(COL_NAME,'0','L')
I get 12345
I want 12345.00

so use FMT() Function to keep the 2 places of the decimal. However, PX is not accepting the above cmd.

[DSguru2B]

FMT() us bit available in px
User the basic transformer if you want to use FMT()

[DS_MJ]

FMT() us bit available in px
User the basic transformer if you want to use FMT()

Thanks...but Dont have basic Transformer in DataStage Designer edition 7.5.1.A. Viewed the palette and was unable to find it to add it.

[ray.wurlod]

You will find the BASIC Transformer stage in the Stage Types branch of the Repository. It was intended not to put it on the Palette by default, though you can of course customize your Palette.

[DS_MJ]

You will find the BASIC Transformer stage in the Stage Types branch of the Repository. It was intended not to put it on the Palette by default, though you can of course customize your Palette. ...

Thank you ray.wurlod and DSguru2B....was able to add the Basic Transformer from the Stage Types using the customized Palette.

Using : TRIM(Fmt(out_fa003_lsmw.PAR_AMOUNT,"R2"),'0','L') in basic transformer am able to get the result

12345.00

However if the input data is 0000000.00

its returning .00

how do I get 0.00...?