Modify Rowset calculation

[toniduquette]

Hello,
I would like to use Modify Rowset to change the values in a column, by dividing the original value by 100.
I created a Modify Rowset task, using existing columns, then hoped to modify a column by using the value such as ${oldCSVFile[4]} /100. However the result was a string, not a decimal though I did a type conversion to decimal when reading the original csv. The error message showed I was trying to insert a string into my Excel decimal field - For input string: "9773 /100". What I wanted from the Modify Rowset was the value 97.73, the original values 9773 divided by 100. Possibly I have to add a new column rather than modify the existing one?
CAn you help?
Thank you!
Here is the pertinent section of my project:

Code: Select all

<readFixedWidth label="Read Data File" inputFilesVariable="${myFileList}" outputRowSetVariable="Data" skipInvalidRecords="true" skipFirstRow="false" recordDelimiter="LF" version="1.0" logLevel="verbose">
			<data trim="none">
				<column index="1" name="Record Code" size="2" type="CHAR" />
				<column index="2" name="Account Number" size="12" type="CHAR" />
				<column index="3" name="Check Number" size="10" type="BIGINT" pattern="####000000" />
				<column index="4" name="Amount" size="12" type="DECIMAL" alignment="right" />
				<column index="5" name="Paid Date" size="8" type="CHAR" />
				<column index="6" name="Issue Date" size="8" type="CHAR" />
				<column index="7" name="Item Ref Number" size="12" type="CHAR" />
				<column index="8" name="Additional Data" size="15" type="CHAR" />
				<column index="9" name="Record Type" size="1" type="CHAR" alignment="right" />
			</data>
		</readFixedWidth>


		<modifyRowSet inputRowSetVariable="${Data}" outputRowSetVariable="OutData" existingColumns="true" version="1.0">
			<modifyColumn index="4" name="Amount" value="${Data[4]} /100" type="DECIMAL" />
		</modifyRowSet>


		<createWorkspace label="Workspace" version="1.0" />


		<writeExcel label="Write Excel File" inputRowSetVariable="${OutData}" outputFile="USBankReconciliationFile${RecMonth}.xls" whenFileExists="overwrite" excelFormat="excel2003" templateFile="/download/excel/USBankReconciliation.xls" sheetName="ChecksPaid" includeHeadings="true" version="2.0">
			<data startRowNumber="2">
				<column index="9" horizontalAlignment="right" />
				<column index="5" pattern="##/##/####" />
			</data>
		</writeExcel>

[Support_Rick]

Try making the following modification:

<modifyColumn index="4" name="Amount" value="${ Data[4] /100 }" type="DECIMAL" />

-- all calculations have to be inside the ${ }

[toniduquette]

Thanks very much - that one gets me every time, I put the braces around the var not the expression!

[toniduquette]

Hi Rick,

I changed the syntax as you suggested:

<createWorkspace label="Workspace" version="1.0" />


<modifyRowSet inputRowSetVariable="${Data}" outputRowSetVariable="OutData" existingColumns="true" version="1.0">
<modifyColumn index="4" name="Amount" value="${ Data[4] /100 }" type="DECIMAL" />
</modifyRowSet>


But I still get this error:

[8099 - modifyRowSet] An unexpected error occurred. Variable not found: Data[4] /100


I have tried with & without spaces before or after the variable etc. Here's the log file showing the rowset exists...

2/27/15 3:24:16 PM INFO Start Date and Time: 2/27/15 3:24:16 PM
2/27/15 3:24:16 PM INFO Job Number: 1418422964994
2/27/15 3:24:16 PM INFO Project Name: /Duquette/ARP Inbound to Excel
2/27/15 3:24:16 PM INFO Submitted By: DUQUETTE
2/27/15 3:24:16 PM INFO GoAnywhere Director 4.6.1 running on OS/400 V6R1M0 (PowerPC)
2/27/15 3:24:16 PM INFO Executing project 'ARP Inbound to Excel'
2/27/15 3:24:16 PM INFO Project location: /linoma/goanywhere/userdata/projects/Duquette/ARP Inbound to Excel.xml
2/27/15 3:24:16 PM INFO Executing module 'Main'
2/27/15 3:24:16 PM INFO Executing task 'createFileList 1.0 (Create List)'
2/27/15 3:24:16 PM INFO FileList variable 'myFileList' was created containing 1 file(s)
2/27/15 3:24:16 PM INFO Finished task 'createFileList 1.0 (Create List)'
2/27/15 3:24:16 PM INFO Executing task 'readFixedWidth 1.0 (Read Data File)'
2/27/15 3:24:16 PM INFO Data parsed successfully and the rowset variable 'Data' was created
2/27/15 3:24:16 PM INFO Finished task 'readFixedWidth 1.0 (Read Data File)'
2/27/15 3:24:16 PM INFO Executing task 'createWorkspace 1.0 (Workspace)'
2/27/15 3:24:16 PM INFO Workspace directory for this job is set to '/linoma/goanywhere/userdata/workspace/1418422964994'.
2/27/15 3:24:16 PM INFO Finished task 'createWorkspace 1.0 (Workspace)'
2/27/15 3:24:16 PM INFO Executing task 'modifyRowSet 1.0'
2/27/15 3:24:16 PM ERROR Variable not found: Data[4] /100
2/27/15 3:24:16 PM INFO Finished project 'ARP Inbound to Excel'
2/27/15 3:24:16 PM ERROR [8099 - modifyRowSet] An unexpected error occurred.
Variable not found: Data[4] /100

Do you have further help?

Thanks!

[Support_Rick]

Can you send me your FULL Project XML and JobLog so I can see what's going on?
Also, can you send a snippet .. maybe 4-5 lines of the Input file you're using that is becoming ${data}.

Thanks!

[toniduquette]

Thank you.
Attached are the project xml and a sample of the data

[toniduquette]

Hi Rick,
I neglected to attach the joblog, here it is.

[Support_Rick]

Upgrade your project to version 2.0 then try it again... click on the "UPGRADE" button from within your project while editing.