Page 1 of 1
calculate from TXT file
Posted: Tue Jul 02, 2013 11:11 am
by jeroen123
Dear Oleg,
please help me out.
I have a txt file as below:
The first {value(0)} is the item number, the second {value(1)} is the sales in pce.
I want to know the total sales per itemnumber.
So I made a TEXT-loop; and within that loop a WHILE loop (while the {value(0)} equals itemnumber) a basicscript evaluate {sales} = {sales} + {value(1)
After this I can write a textfile with the total sales per item.
my question is: where to assign the first initial value of the first itemnumber of the textfile?
I cannot do AFTER the txt-loop; cause then it will change all the time
I cannot do before the txt-loop; cause the file has not been read by then...
Any clue?
81000,1,20,190
81000,15,17,5,190
81000,1,20,190
81000,1,20,190
81001,0,20,190
81002,1,20,190
81002,16,17,5,190
81002,1,20,190
81002,1,20,190
81002,1,20,190
81002,1,20,190
81003,1,20,190
81003,17,17,5,190
81004,1,20,190
81004,13,17,5,190
81004,1,0,190
81004,1,20,190
Re: calculate from TXT file
Posted: Tue Jul 02, 2013 12:40 pm
by Oleg
Very interesting task.
Typical algorithm is similar to my example. I hope this will help you.
Look at my example below:
Code: Select all
;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|953684383
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task31"
Hide=INTEGER|0
ID=INTEGER|2024970696
LogOnAsUser=INTEGER|1
Name=STRING|"sales per item"
OnErrorTaskID=INTEGER|0
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0
[Actions]
Action1=FOLDER
Action10=FOLDER
Action11=FOLDER
Action12=FOLDER
Action13=FOLDER
Action14=FOLDER
Action15=FOLDER
Action16=FOLDER
Action17=FOLDER
Action18=FOLDER
Action19=FOLDER
Action2=FOLDER
Action20=FOLDER
Action21=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER
[Actions\Action1]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""LINE"" with value """""
Params=FOLDER
[Actions\Action1\Params]
expand=STRING|"0"
varkind=STRING|"1"
varname=STRING|"LINE"
[Actions\Action10]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"
[Actions\Action11]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//reset values"
Params=FOLDER
[Actions\Action11\Params]
comment=STRING|"reset values"
[Actions\Action12]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""SALES"" with value ""0"""
Params=FOLDER
[Actions\Action12\Params]
expand=STRING|"0"
varname=STRING|"SALES"
varvalue=STRING|"0"
[Actions\Action13]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""OLDITEM"" with value ""{line(0)}"""
Params=FOLDER
[Actions\Action13\Params]
expand=STRING|"1"
varname=STRING|"OLDITEM"
varvalue=STRING|"{line(0)}"
[Actions\Action14]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"
[Actions\Action15]
ActionID=STRING|"BASIC_EVALUATE"
Enabled=INTEGER|-1
Name=STRING|"Evaluate"
Params=FOLDER
[Actions\Action15\Params]
expression=STRING|"{sales}+{line(1)}"
variable=STRING|"SALES"
[Actions\Action16]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
[Actions\Action17]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then"
Params=FOLDER
[Actions\Action17\Params]
case=STRING|"0"
operator=STRING|"8"
type=STRING|"0"
value1=STRING|"{olditem}"
[Actions\Action18]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//output last item after the loop"
Params=FOLDER
[Actions\Action18\Params]
comment=STRING|"output last item after the loop"
[Actions\Action19]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""TXT"" with value ""{txt}Item: {olditem}; Sales: {sales}{EOL}"""
Params=FOLDER
[Actions\Action19\Params]
expand=STRING|"1"
varname=STRING|"TXT"
varvalue=STRING|"{txt}Item: {olditem}; Sales: {sales}{EOL}"
[Actions\Action2]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""OLDITEM"" with value """""
Params=FOLDER
[Actions\Action2\Params]
expand=STRING|"0"
varkind=STRING|"1"
varname=STRING|"OLDITEM"
[Actions\Action20]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"
[Actions\Action21]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""{txt}"""
Params=FOLDER
[Actions\Action21\Params]
icon=STRING|"1"
msg0=STRING|"{txt}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"
[Actions\Action3]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""SALES"" with value """""
Params=FOLDER
[Actions\Action3\Params]
expand=STRING|"0"
varkind=STRING|"1"
varname=STRING|"SALES"
[Actions\Action4]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""TXT"" with value """""
Params=FOLDER
[Actions\Action4\Params]
expand=STRING|"0"
varkind=STRING|"1"
varname=STRING|"TXT"
[Actions\Action5]
ActionID=STRING|"A_LOOP_TEXT"
Enabled=INTEGER|-1
Name=STRING|"Text Loop"
Params=FOLDER
[Actions\Action5\Params]
destvar=STRING|"LINE"
line0=STRING|"81000,1,20,190"
line1=STRING|"81000,15,17,5,190"
line10=STRING|"81002,1,20,190"
line11=STRING|"81003,1,20,190"
line12=STRING|"81003,17,17,5,190"
line13=STRING|"81004,1,20,190"
line14=STRING|"81004,13,17,5,190"
line15=STRING|"81004,1,0,190"
line16=STRING|"81004,1,20,190"
line2=STRING|"81000,1,20,190"
line3=STRING|"81000,1,20,190"
line4=STRING|"81001,0,20,190"
line5=STRING|"81002,1,20,190"
line6=STRING|"81002,16,17,5,190"
line7=STRING|"81002,1,20,190"
line8=STRING|"81002,1,20,190"
line9=STRING|"81002,1,20,190"
linecount=STRING|"17"
sourcetext=STRING|"1"
[Actions\Action6]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then"
Params=FOLDER
[Actions\Action6\Params]
case=STRING|"0"
operator=STRING|"1"
type=STRING|"0"
value1=STRING|"{olditem}"
value2=STRING|"{line(0)}"
[Actions\Action7]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//output result"
Params=FOLDER
[Actions\Action7\Params]
comment=STRING|"output result"
[Actions\Action8]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then"
Params=FOLDER
[Actions\Action8\Params]
case=STRING|"0"
operator=STRING|"8"
type=STRING|"0"
value1=STRING|"{olditem}"
[Actions\Action9]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""TXT"" with value ""{txt}Item: {olditem}; Sales: {sales}{EOL}"""
Params=FOLDER
[Actions\Action9\Params]
expand=STRING|"1"
varname=STRING|"TXT"
varvalue=STRING|"{txt}Item: {olditem}; Sales: {sales}{EOL}"
Re: calculate from TXT file
Posted: Fri Oct 04, 2013 2:31 pm
by jeroen123
a bit late;
but this works
Thanks!
Re: calculate from TXT file
Posted: Thu Oct 10, 2013 2:22 pm
by jeroen123
dear Oleg;
I am working on this task,
now I try to calculate totals from a list of 1600 invoice lines, like:
00025019,17,3,7261,AG
00025019,11,3,7261,AG
00025019,11,2,7261,AG
00025019,22,11,7261,AG
00025028,22,4,9210,AG
00025028,17,3,9210,AG
00025028,10,1,9210,AG
00025028,17,5,9210,AG
00025028,21,3,9210,AG
00025028,9,1,9210,AG
00025028,9,0,9210,AG
00025028,34,7,9210,AG
00025028,34,10,9210,AG
Now halfway I get an error:
E: 10-10-2013 15:12:52: Error in expression "32762+8"
E: 10-10-2013 15:12:52: An error occurred. Step #28 (Evaluate berekenen verkoop totaal).
- check calc.jpg (120.95 KiB) Viewed 19428 times
the black is the calculation RT make and data from RT; the red is the check calculation that I made in EXCEL:
untill row 145 all goes well (see the last columns calculation is zero difference)
then in row 146 it should add 84 but it adds just 33
at first sight it seems a very simple calculation: why error on this??
is there a maximum level to calculations?
Re: calculate from TXT file
Posted: Thu Oct 10, 2013 3:02 pm
by Oleg
BASIC engine uses 16-bit integer values by default.
Integer values are from -32768 to 32767
If you use expression like "32762+8" then BASIC uses 16-bit INTEGER values and get overflow error.
You can use LONG (32-bit integer) or REAL (64-bit real number) datatype
In this case you should use such expressions
For LONG use 32762&+8& (symbol & says that basic must use LONG value)
For REAL use 32762#+8# (symbol # says that it is a REAL value)
In your task use expression like this:
{sales}#+{line(1)}#
Re: calculate from TXT file
Posted: Thu Oct 10, 2013 4:28 pm
by jeroen123
Life is so simple if you know how
works!
Thanks!