Any function to remove the leading zeros

[ravij]

Hi,

I have data like

ColA(Hash) ColB(Src)
2577 , 0002577
13 , 0000013
345 , 0000345

I need to match these col's and based on this condition I need to populate other fields into the target.
Here both col values r same but colB is having some zeros preceding the value. How can I eliminate the zeros?
Note: Here the no. of zeros in all the values are not same

Any answer can be appreciated.
Thanks in advance

[kumar_s]

Hi,
Use Conver(Column,'0','')

-Kumar

[ArndW]

I often just use (In.ColumnName + 0) to force a conversion from string to integer; or you can explicitly use TRIM(In.ColumnName,'0','L') or any number of format statements, such as FMT(In.ColumnName,'Z')

[kcshankar]

Hi Ravij,

Code: Select all

ColA(Hash) ColB(Src) 
2577 , 0002577 
13 , 0000013 
345 , 0000345 
Here both col values r same but colB is having some zeros preceding the value. How can I eliminate the zeros? 
Here the no. of zeros in all the values are not same 

Use Format function to get the required result.

FMT("ColB(Src)", "RZ")

Z,When used with the Q format, only the trailing fractional zeros are suppressed, and a 0 exponent is suppressed.
For more information gothru the document.


Regards
kcs

[ravij]

Thanks for all,

And Can I append the zeros for the same problem which was disscussed above instead of removing the zeros?
If there is any function plz let me know.

thnaks in advance

[ravij]

Sorry
Smalll correction

I want to append the zeros for the ColA. And It should be equal to the ColB' zeros.

thanks

[ArndW]

ravij,

appending zeroes will change the value of the number. If you wish to format a number with leading zeroes then you should use an explicit FMT command such as FMT(In.ColumnName,'6"0"R') which will make the value "0123" into "000123"

[ravij]

Hi Kumar,

This function is not working properly and its giving empty values(no values in the field)

Use Conver(Column,'0','')

-Kumar

The remaining functions which suggested by Arnd working fine.

And my other doubt is the ColA which is having leading zeros is my Hash file. I need to match with it with Source(Seq file) Col. Then Where can I define that function?

thanks in advance.

[kumar_s]

Hi,
I hope it should work the same way in server editon as in parallel.

Better option to over ride the hindarance would be make both the field as decimal. So that the values will be automatically preceded by zeros.

-Kumar

[kcshankar]

Hi Ravij,

Code: Select all

This function is not working properly and its giving empty values(no values in the field) 

I think it is Convert("0", "", ColB).


For your other option of matching ColA with ColB

Code: Select all

And my other doubt is the ColA which is having leading zeros is my Hash file. I need to match with it with Source(Seq file) Col. Then Where can I define that function? 

.
As Arndw suggested if you use FMT(In.ColumnName,'6"0"R').
you will get fixed length(6) string,since no of zeros in your column is not same you won't get the required result.
Try this out,
Declare a Stage variable to calculate the len(colb).
Pass it in FMT function.
Hope it works


Regards
kcs

[ravij]

Thanks for your suggestions.

I will try n come back.
One doubt: Can I use EReplace Function to eliminate the leading zeros?

thnks in advance

[ravij]

Hi ArndW

FMT(In.ColumnName,'6"0"R') .

What is 'In' in the function? what does it represent?

Your assistance can be appreciated

[ravij]

Sorry for all
I am not getting the output when use the function which u suggested in the Key Expressin of Hash file(in XFM). But I am getting the required output by using the following function.

Ereplace(Space(10-Len(Trim(ColName)))," ","0",-1,1) : Trim(ColName)

Why I am not able to get the data by using other functions which u suggested?
Any other suggestions plz

thanks in advance

[ArndW]

Ravij,

The EREPLACE function will replace all occurrences of the string; so in your case if you do use EREPLACE you will turn "0000200" into "2".

In a DataStage transformation all columns are referred to in derivations in the form {LinkName}.{ColumnName}; since I don't know what your exact names are, I always utilize the vague form of "In.ColumnName" and let you fill in the appropriate values for your job.

[ravij]

Arnd,

Ereplace(Space(10-Len(Trim(ColName)))," ","0",-1,1) : Trim(ColName)

Could you explain the function which is mentioned above plz?

thnaks in advance