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
- Posts: 7
- Joined: Thu Aug 21, 2014 3:43 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:
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 Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
Try making the following modification:
<modifyColumn index="4" name="Amount" value="${ Data[4] /100 }" type="DECIMAL" />
-- all calculations have to be inside the ${ }
<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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
- Posts: 7
- Joined: Thu Aug 21, 2014 3:43 pm
Thanks very much - that one gets me every time, I put the braces around the var not the expression!
- Posts: 7
- Joined: Thu Aug 21, 2014 3:43 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!
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 Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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!
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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
- Posts: 7
- Joined: Thu Aug 21, 2014 3:43 pm
Thank you.
Attached are the project xml and a sample of the data
Attached are the project xml and a sample of the data
Attachments
ARP Inbound to Excel.xml
DataSample.txt
Project xml
(3.08 KiB) Downloaded 786 times
(3.08 KiB) Downloaded 786 times
DataSample.txt
Sample data
(739 Bytes) Downloaded 820 times
(739 Bytes) Downloaded 820 times
- Posts: 7
- Joined: Thu Aug 21, 2014 3:43 pm
Hi Rick,
I neglected to attach the joblog, here it is.
I neglected to attach the joblog, here it is.
Attachments
1418422964989.log
Job log
(10.61 KiB) Downloaded 776 times
(10.61 KiB) Downloaded 776 times
- Support Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
8 posts
Page 1 of 1