Modify Rowset calculation

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
8 posts Page 1 of 1

toniduquette

Posts: 7
Joined: Thu Aug 21, 2014 3:43 pm

Post by toniduquette » Tue Feb 03, 2015 4:51 pm
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

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Tue Feb 03, 2015 5:02 pm
Try making the following modification:

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

-- all calculations have to be inside the ${ }
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

toniduquette

Posts: 7
Joined: Thu Aug 21, 2014 3:43 pm

Post by toniduquette » Fri Feb 27, 2015 5:36 pm
Thanks very much - that one gets me every time, I put the braces around the var not the expression!

toniduquette

Posts: 7
Joined: Thu Aug 21, 2014 3:43 pm

Post by toniduquette » Fri Feb 27, 2015 6:29 pm
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

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Mon Mar 02, 2015 6:13 pm
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!
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

toniduquette

Posts: 7
Joined: Thu Aug 21, 2014 3:43 pm

Post by toniduquette » Thu Mar 19, 2015 5:07 pm
Thank you.
Attached are the project xml and a sample of the data
Attachments
ARP Inbound to Excel.xml
Project xml
(3.08 KiB) Downloaded 304 times

DataSample.txt
Sample data
(739 Bytes) Downloaded 294 times

toniduquette

Posts: 7
Joined: Thu Aug 21, 2014 3:43 pm

Post by toniduquette » Thu Mar 19, 2015 6:19 pm
Hi Rick,
I neglected to attach the joblog, here it is.
Attachments
1418422964989.log
Job log
(10.61 KiB) Downloaded 287 times

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Tue Mar 24, 2015 1:34 am
Upgrade your project to version 2.0 then try it again... click on the "UPGRADE" button from within your project while editing.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
8 posts Page 1 of 1