Metadata to insert decimal at specific location

wkerr

• September 07, 2017 00:00

• Answered

Follow

Hi,

Reading a large fixed length file with 100+ fields and would like to insert decimal point during the read of the file.
example of what a row looks like below:

01A0000100001AB

if the type for each field was all strings it will be converted to:

field1: "01"
field2: "A"
field3: "00001"
field4: "00001"
field5: "AB"

All the metadata including decimal position is defined in an external document that is being converted into metadata.fmt .

meta field for field3 and field4 looks like below:

<Field format="0.0000" length="5" name="field3" scale="4" size="5" type="decimal"/>
<Field format="0.0000" length="5" name="field4" scale="4" size="5" type="decimal"/>

and it outputs:

field3: 1.0000
field4: 1.0000

What i would like is for field3, field4 to be:

field3: 0.0001
field4: 0.0001

What format would be needed? Or is it possible to use a reformat transformation and dynamically transform all decimal types based on the metadata?

Thanks in advance!

• 

  Vladimir Barton

  • September 11, 2017 11:08

  0

  Comment actions Permalink

  Hello wkerr,
  from your example, it seems that it is not possible to read the string “00001” directly into the decimal 0.0001. As you rightly indicated, you would have to take advantage of the Reformat component to achieve the desired format. I would suggest taking this path:
  
  

  • Read the input fields (field3 and field4) as strings (“00001”).
  
  
  • Add the Reformat component into your graph and assign the output metadata as per your example (<Field format="0.0000" length="5" name="fieldX" scale="4" size="5" type="decimal"/>).
  
  
  • Use the following transformation in the reformat transform function:

  
  

  $out.0.FieldX = str2decimal(charAt($in.0.FieldX, 0) + "." + substring($in.0.FieldX, 1));

  
  Note: the charAt() function grabs the first character (index position 0 in the input string) and concatenates it with the dot character and the substring of the input string (from the index position 1 onwards).
  If this does not meet your needs (for example due to the higher complexity of the actual input file), feel free to get back to us with more details.
  Regards,

Please sign in to leave a comment.