Metadata to insert decimal at specific location

Support/help with CloverETL implementation problems

wkerr
Posts: 1
Joined: Thu Sep 07, 2017 4:30 pm

Metadata to insert decimal at specific location

Postby wkerr » Thu Sep 07, 2017 5:57 pm

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!

bartonv
Posts: 32
Joined: Wed May 03, 2017 12:10 pm

Re: Metadata to insert decimal at specific location

Postby bartonv » Mon Sep 11, 2017 12:08 pm

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:

  1. Read the input fields (field3 and field4) as strings (“00001”).
  2. 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"/>).
  3. Use the following transformation in the reformat transform function:

Code: Select all

$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,
---
Vladimir Barton
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support