Re: [U2] Running total
Kate, This gives strange results if F1 is multi-valued so attribute 2 of the dict item needs to be: @1+SUM(F1) Thanks, Don Robinson From: Kate Stanton k...@walstan.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Tuesday, July 2, 2013 6:32 PM Subject: Re: [U2] Running total Are you looking for @1? This gives a column containing running total (I learned it from this list) DICT AP F1 invoice value (A type or I type) T1 running total I type: F2: @1 + F1 So, listing 3 items gives: LIST AP TOTAL F1 KATE 10:26:30am 03 Jul 2013 PAGE 1 APID. INV. T1. AMT. 101*850563 52,578.24 52,578.24 101*850568 -86.24 52,492.00 101*850569 86.24 52,578.24 *** 52,578.24 3 records listed. On 2 July 2013 20:29, Martin Phillips martinphill...@ladybridge.com wrote: Hi, As far as I know, the only way to do this in U2 is to use either a common variable or one of the @USERn variables in a subroutine to accumulate the result. Handling multi-values is not hard (as below). Getting this to work for breakpoint lines is a bit more awkward. For example, using UniVerse's demonstration sales database (INITIALIZE.DEMO), I can create an I-type named ACCUM that reads I subr('ACCUMULATE', ITEM.TOTAL) MD2 Cumulative 8R M BOUGHT and a program catalogued as ACCUMULATE function accumulate(item) common /total/tot if @ni = 1 then tot = 0 x = '' n = dcount(item, @vm) for i = 1 to n tot += item1,i x1,i = tot next i return (x) end I can then do LIST ORDERS PROD.NO SELL QTY ITEM.TOTAL ACCUM @ID.. Product No Sell. Qty. Item Total Cumulative 10002 605 $40.00 1 $40.00 40.00 501 $5.00 1 $5.00 45.00 502 $5.00 1 $5.00 50.00 504 $5.00 1 $5.00 55.00 10006 112 $6.00 3 $18.00 73.00 10004 704 $115.00 1 $115.00 188.00 301 $10.00 9 $90.00 278.00 10005 502 $5.00 9 $45.00 323.00 10003 202 $5.00 10 $50.00 373.00 204 $5.00 10 $50.00 423.00 10001 112 $6.00 7 $42.00 465.00 418 $27.00 4 $108.00 573.00 704 $115.00 1 $115.00 688.00 10007 301 $10.00 3 $30.00 718.00 Maybe Rocket can be persuaded to implement the CUMULATIVE field modifier keyword that we have in QM to make this trivial Martin Phillips Ladybridge Systems Ltd 17b Coldstream Lane, Hardingstone, Northampton NN4 6DB, England +44 (0)1604-709200 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto: u2-users-boun...@listserver.u2ug.org] On Behalf Of Wjhonson Sent: 02 July 2013 01:36 To: u2-users@listserver.u2ug.org Subject: [U2] Running total Teach me again how to do a running total in a Access/Recall/English report. I haven't done one in years, but I seem to recall that it's possible. ___ U2-Users mailing list mailto:U2-Users@listserver.u2ug.org U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Kate Stanton Walstan Systems Ltd 4 Kelmarna Ave, Herne Bay, Auckland 1011, New Zealand Phone: + 64 9 360 5310 Mobile: + 64 21 400 486 Email: k...@walstan.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Running total
But correct me if I'm mistaking this, but doesn't this mean you need a special extra dict entry for any column for which you want a running total? So if I have a file with ten different fields that hold raw dollar amounts and I may want a running total on any particular one of them, I would need ten more dict items, one for each ? Or am I reading this solution wrong? -Original Message- From: Don Robinson donr_w...@yahoo.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Wed, Jul 3, 2013 6:54 am Subject: Re: [U2] Running total Kate, This gives strange results if F1 is multi-valued so attribute 2 of the dict item needs to be: @1+SUM(F1) Thanks, Don Robinson From: Kate Stanton k...@walstan.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Tuesday, July 2, 2013 6:32 PM Subject: Re: [U2] Running total Are you looking for @1? This gives a column containing running total (I learned it from this list) DICT AP F1invoice value (A type or I type) T1running total I type: F2: @1 + F1 So, listing 3 items gives: LIST AP TOTAL F1 KATE 10:26:30am 03 Jul 2013 PAGE1 APID. INV. T1. AMT. 101*850563 52,578.2452,578.24 101*850568 -86.2452,492.00 101*850569 86.2452,578.24 *** 52,578.24 3 records listed. On 2 July 2013 20:29, Martin Phillips martinphill...@ladybridge.com wrote: Hi, As far as I know, the only way to do this in U2 is to use either a common variable or one of the @USERn variables in a subroutine to accumulate the result. Handling multi-values is not hard (as below). Getting this to work for breakpoint lines is a bit more awkward. For example, using UniVerse's demonstration sales database (INITIALIZE.DEMO), I can create an I-type named ACCUM that reads I subr('ACCUMULATE', ITEM.TOTAL) MD2 Cumulative 8R M BOUGHT and a program catalogued as ACCUMULATE function accumulate(item) common /total/tot if @ni = 1 then tot = 0 x = '' n = dcount(item, @vm) for i = 1 to n tot += item1,i x1,i = tot next i return (x) end I can then do LIST ORDERS PROD.NO SELL QTY ITEM.TOTAL ACCUM @ID.. Product No Sell. Qty. Item Total Cumulative 10002605$40.001 $40.00 40.00 501 $5.001 $5.00 45.00 502 $5.001 $5.00 50.00 504 $5.001 $5.00 55.00 10006112 $6.003 $18.00 73.00 10004704$115.001$115.00 188.00 301$10.009 $90.00 278.00 10005502 $5.009 $45.00 323.00 10003202 $5.0010 $50.00 373.00 204 $5.0010 $50.00 423.00 10001112 $6.007 $42.00 465.00 418$27.004$108.00 573.00 704$115.001$115.00 688.00 10007301$10.003 $30.00 718.00 Maybe Rocket can be persuaded to implement the CUMULATIVE field modifier keyword that we have in QM to make this trivial Martin Phillips Ladybridge Systems Ltd 17b Coldstream Lane, Hardingstone, Northampton NN4 6DB, England +44 (0)1604-709200 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto: u2-users-boun...@listserver.u2ug.org] On Behalf Of Wjhonson Sent: 02 July 2013 01:36 To: u2-users@listserver.u2ug.org Subject: [U2] Running total Teach me again how to do a running total in a Access/Recall/English report. I haven't done one in years, but I seem to recall that it's possible. ___ U2-Users mailing list mailto:U2-Users@listserver.u2ug.org U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Kate Stanton Walstan Systems Ltd 4 Kelmarna Ave, Herne Bay, Auckland 1011, New Zealand Phone: + 64 9 360 5310 Mobile: + 64 21 400 486 Email: k...@walstan.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Running total
Thanks, Don. I had not tried to use it with multi-values. Cheers, Kate On 4 July 2013 01:54, Don Robinson donr_w...@yahoo.com wrote: Kate, This gives strange results if F1 is multi-valued so attribute 2 of the dict item needs to be: @1+SUM(F1) Thanks, Don Robinson From: Kate Stanton k...@walstan.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Tuesday, July 2, 2013 6:32 PM Subject: Re: [U2] Running total Are you looking for @1? This gives a column containing running total (I learned it from this list) DICT AP F1invoice value (A type or I type) T1running total I type: F2: @1 + F1 So, listing 3 items gives: LIST AP TOTAL F1 KATE 10:26:30am 03 Jul 2013 PAGE1 APID. INV. T1. AMT. 101*850563 52,578.2452,578.24 101*850568 -86.2452,492.00 101*850569 86.2452,578.24 *** 52,578.24 3 records listed. On 2 July 2013 20:29, Martin Phillips martinphill...@ladybridge.com wrote: Hi, As far as I know, the only way to do this in U2 is to use either a common variable or one of the @USERn variables in a subroutine to accumulate the result. Handling multi-values is not hard (as below). Getting this to work for breakpoint lines is a bit more awkward. For example, using UniVerse's demonstration sales database (INITIALIZE.DEMO), I can create an I-type named ACCUM that reads I subr('ACCUMULATE', ITEM.TOTAL) MD2 Cumulative 8R M BOUGHT and a program catalogued as ACCUMULATE function accumulate(item) common /total/tot if @ni = 1 then tot = 0 x = '' n = dcount(item, @vm) for i = 1 to n tot += item1,i x1,i = tot next i return (x) end I can then do LIST ORDERS PROD.NO SELL QTY ITEM.TOTAL ACCUM @ID.. Product No Sell. Qty. Item Total Cumulative 10002605$40.001 $40.00 40.00 501 $5.001 $5.00 45.00 502 $5.001 $5.00 50.00 504 $5.001 $5.00 55.00 10006112 $6.003 $18.00 73.00 10004704$115.001$115.00 188.00 301$10.009 $90.00 278.00 10005502 $5.009 $45.00 323.00 10003202 $5.0010 $50.00 373.00 204 $5.0010 $50.00 423.00 10001112 $6.007 $42.00 465.00 418$27.004$108.00 573.00 704$115.001$115.00 688.00 10007301$10.003 $30.00 718.00 Maybe Rocket can be persuaded to implement the CUMULATIVE field modifier keyword that we have in QM to make this trivial Martin Phillips Ladybridge Systems Ltd 17b Coldstream Lane, Hardingstone, Northampton NN4 6DB, England +44 (0)1604-709200 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto: u2-users-boun...@listserver.u2ug.org] On Behalf Of Wjhonson Sent: 02 July 2013 01:36 To: u2-users@listserver.u2ug.org Subject: [U2] Running total Teach me again how to do a running total in a Access/Recall/English report. I haven't done one in years, but I seem to recall that it's possible. ___ U2-Users mailing list mailto:U2-Users@listserver.u2ug.org U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Kate Stanton Walstan Systems Ltd 4 Kelmarna Ave, Herne Bay, Auckland 1011, New Zealand Phone: + 64 9 360 5310 Mobile: + 64 21 400 486 Email: k...@walstan.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Kate Stanton Walstan Systems Ltd 4 Kelmarna Ave, Herne Bay, Auckland 1011, New Zealand Phone: + 64 9 360 5310 Mobile: + 64 21 400 486 Email: k...@walstan.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Running total
Can anyone explain this situation better. I've only ever used the @ to simplify a few I-descriptors. I have a dict item SIZE that is just the LEN(@RECORD) So in this thread we've seen two EVAL solutions to a running total LIST VOC SIZE EVAL @1+SIZE LIST VOC SIZE EVAL SIZE + @2;@ In the first example is the @1 just saving the previous result of THIS eval ? And then in the second example does the fact that SIZE is first in the Eval, effectively *push* the accumulator into position two or something? So that's why you need @2 ? And then what would be the meaning of ;@ at the end of that? Pretty confusing stuff! ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Running total
I believe the expression in the second example is referred to as a compound expression? Can't put my finger on the exact documentation but I seem to recall that compound expressions are broken up into parts (for want of a better term) delimited by a ';' and each part can be referenced throughout the entire expression by an @ number e..g. @1 refers to the first part and the nth part is @n etc. So for: LIST VOC SIZE EVAL SIZE + @2;@ My take on how this works is the the first part of the expression (SIZE + @2) can be referenced later on as @1, The second part is referenced as @ (instead of @2) and I believe is used to refer to the result of the entire expression (sum of all the parts). Can @ in this case also be referenced as @2? It probably could but would break the expression. Therefore the reference to @2 is causing the expression to grab the entire result using contents of @ (via @2 thus keeping it separate to @) and add it to SIZE which then becomes the new result (@) of the expressions and the whole thing is repeated. Does that sound about tight? Or have I got it way wrong? Cheers Peter Peter Cheney Universe Engineer t 07 3017 8837 | f 07 3002 8400 e peter.che...@firstmac.com.au | w www.firstmac.com.au From: u2-users-boun...@listserver.u2ug.org [u2-users-boun...@listserver.u2ug.org] on behalf of Wjhonson [wjhon...@aol.com] Sent: Thursday, 4 July 2013 7:31 AM To: u2-users@listserver.u2ug.org; donr_w...@yahoo.com Subject: Re: [U2] Running total Can anyone explain this situation better. I've only ever used the @ to simplify a few I-descriptors. I have a dict item SIZE that is just the LEN(@RECORD) So in this thread we've seen two EVAL solutions to a running total LIST VOC SIZE EVAL @1+SIZE LIST VOC SIZE EVAL SIZE + @2;@ In the first example is the @1 just saving the previous result of THIS eval ? And then in the second example does the fact that SIZE is first in the Eval, effectively *push* the accumulator into position two or something? So that's why you need @2 ? And then what would be the meaning of ;@ at the end of that? Pretty confusing stuff! ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users --- Note: This email (inc all attachments) is for the use of the intended recipient(s) only. Privileged or confidential information may be contained in this communication. If you have received this email in error, please notify the sender immediately and then delete all copies of this message from your computer network. If you are not the intended recipient, you must not keep, use, disclose, copy or distribute this email without the author's prior permission. If you are the intended recipient and you do not wish to receive similar electronic messages from us in future, then please respond to the sender to this effect. We have taken precautions to minimise the risk of transmitting software viruses, but advise you to carry out your own virus checks on this email and its attachments. We do not accept liability for any loss or damage caused by software viruses and do not represent that this transmission is free from viruses or other defects. Firstmac Limited (ABN 59 094 145 963) (AFSL 290600) --- ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Running total
I could only find doc for this in the System Description manual, where it talks about using @ in I-type expressions, where @ is the previous one (just resolved), @1 is result of first sentence (separated by ;), @2 is result of 2nd sentence, etc. I could not see an example of using the result from the previous record, but it works. BTW, with multi-values, ADDS(@1,F26) works. V1 of first record adds to V1 of 2nd, etc. I can't think of where we could use it, but it depends on your file structure. On 4 July 2013 11:47, Peter Cheney peter.che...@firstmac.com.au wrote: I believe the expression in the second example is referred to as a compound expression? Can't put my finger on the exact documentation but I seem to recall that compound expressions are broken up into parts (for want of a better term) delimited by a ';' and each part can be referenced throughout the entire expression by an @ number e..g. @1 refers to the first part and the nth part is @n etc. So for: LIST VOC SIZE EVAL SIZE + @2;@ My take on how this works is the the first part of the expression (SIZE + @2) can be referenced later on as @1, The second part is referenced as @ (instead of @2) and I believe is used to refer to the result of the entire expression (sum of all the parts). Can @ in this case also be referenced as @2? It probably could but would break the expression. Therefore the reference to @2 is causing the expression to grab the entire result using contents of @ (via @2 thus keeping it separate to @) and add it to SIZE which then becomes the new result (@) of the expressions and the whole thing is repeated. Does that sound about tight? Or have I got it way wrong? Cheers Peter Peter Cheney Universe Engineer t 07 3017 8837 | f 07 3002 8400 e peter.che...@firstmac.com.au | w www.firstmac.com.au From: u2-users-boun...@listserver.u2ug.org [ u2-users-boun...@listserver.u2ug.org] on behalf of Wjhonson [ wjhon...@aol.com] Sent: Thursday, 4 July 2013 7:31 AM To: u2-users@listserver.u2ug.org; donr_w...@yahoo.com Subject: Re: [U2] Running total Can anyone explain this situation better. I've only ever used the @ to simplify a few I-descriptors. I have a dict item SIZE that is just the LEN(@RECORD) So in this thread we've seen two EVAL solutions to a running total LIST VOC SIZE EVAL @1+SIZE LIST VOC SIZE EVAL SIZE + @2;@ In the first example is the @1 just saving the previous result of THIS eval ? And then in the second example does the fact that SIZE is first in the Eval, effectively *push* the accumulator into position two or something? So that's why you need @2 ? And then what would be the meaning of ;@ at the end of that? Pretty confusing stuff! ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users --- Note: This email (inc all attachments) is for the use of the intended recipient(s) only. Privileged or confidential information may be contained in this communication. If you have received this email in error, please notify the sender immediately and then delete all copies of this message from your computer network. If you are not the intended recipient, you must not keep, use, disclose, copy or distribute this email without the author's prior permission. If you are the intended recipient and you do not wish to receive similar electronic messages from us in future, then please respond to the sender to this effect. We have taken precautions to minimise the risk of transmitting software viruses, but advise you to carry out your own virus checks on this email and its attachments. We do not accept liability for any loss or damage caused by software viruses and do not represent that this transmission is free from viruses or other defects. Firstmac Limited (ABN 59 094 145 963) (AFSL 290600) --- ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Kate Stanton Walstan Systems Ltd 4 Kelmarna Ave, Herne Bay, Auckland 1011, New Zealand Phone: + 64 9 360 5310 Mobile: + 64 21 400 486 Email: k...@walstan.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Running total
You can also see what the I-type does by DLIST yourfile yourdictitem It's the equivalent of VLIST for I-Types... -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Peter Cheney Sent: Thursday, 4 July 2013 9:48 AM To: U2 Users List Subject: Re: [U2] Running total I believe the expression in the second example is referred to as a compound expression? Can't put my finger on the exact documentation but I seem to recall that compound expressions are broken up into parts (for want of a better term) delimited by a ';' and each part can be referenced throughout the entire expression by an @ number e..g. @1 refers to the first part and the nth part is @n etc. So for: LIST VOC SIZE EVAL SIZE + @2;@ My take on how this works is the the first part of the expression (SIZE + @2) can be referenced later on as @1, The second part is referenced as @ (instead of @2) and I believe is used to refer to the result of the entire expression (sum of all the parts). Can @ in this case also be referenced as @2? It probably could but would break the expression. Therefore the reference to @2 is causing the expression to grab the entire result using contents of @ (via @2 thus keeping it separate to @) and add it to SIZE which then becomes the new result (@) of the expressions and the whole thing is repeated. Does that sound about tight? Or have I got it way wrong? Cheers Peter Peter Cheney Universe Engineer t 07 3017 8837 | f 07 3002 8400 e peter.che...@firstmac.com.au | w www.firstmac.com.au From: u2-users-boun...@listserver.u2ug.org [u2-users-boun...@listserver.u2ug.org] on behalf of Wjhonson [wjhon...@aol.com] Sent: Thursday, 4 July 2013 7:31 AM To: u2-users@listserver.u2ug.org; donr_w...@yahoo.com Subject: Re: [U2] Running total Can anyone explain this situation better. I've only ever used the @ to simplify a few I-descriptors. I have a dict item SIZE that is just the LEN(@RECORD) So in this thread we've seen two EVAL solutions to a running total LIST VOC SIZE EVAL @1+SIZE LIST VOC SIZE EVAL SIZE + @2;@ In the first example is the @1 just saving the previous result of THIS eval ? And then in the second example does the fact that SIZE is first in the Eval, effectively *push* the accumulator into position two or something? So that's why you need @2 ? And then what would be the meaning of ;@ at the end of that? Pretty confusing stuff! ** IMPORTANT MESSAGE * This e-mail message is intended only for the addressee(s) and contains information which may be confidential. If you are not the intended recipient please advise the sender by return email, do not use or disclose the contents, and delete the message and any attachments from your system. Unless specifically indicated, this email does not constitute formal advice or commitment by the sender or the Commonwealth Bank of Australia (ABN 48 123 123 124) or its subsidiaries. We can be contacted through our web site: commbank.com.au. If you no longer wish to receive commercial electronic messages from us, please reply to this e-mail by typing Unsubscribe in the subject line. ** ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users