Summing transactions - JBASE 5.1

[thomas.r...@gmail.com]

I have a transaction table that looks like this: 

0 1 4 5 9

TRANS#...... ACCOUNT#. TR AMT.. TRANS DT SOURCE............

168401X16029    100541    -0.06 02-07-14                   

168401X15779    100542    -0.07 02-07-14                   

 168401X6246    100545    -0.20 02-07-14                   

   164522X10    100547    16.87 01-11-13                  3

   164522X11    100547    35.32 01-11-13                  3

   164513X44    100550     0.77 01-14-13                   

 168401X2867    100554    -0.01 02-07-14                   

    165106X3    100555   250.00 03-11-13                  3

    165466X5    100555   250.00 04-15-13                  3

168401X14627    100555    -0.01 02-07-14                   

I want to put together a dictionary item that will, for each account number (attribute 1) sum every transaction amount (attribute 4) from the same date (attribute 5) where the source (attribute 9) has a value of "3".

I have been banging my head against this and I don't think it is possible.  Am I wrong?  

[Kevin Powick]

A dict item does not provide summary information for an entire file.

Unfortunately, you haven't really described your end goal, only what you're trying to do with a dict item.  What are trying to ultimately accomplish? Are you trying to produce a report with specific information, such a summary totals?

--

Kevin Powick

[Peter Mowatt]

Sort of a hybrid situation. If they  are all single valued, then it should be straight forward. Your example may not be exposing the nuances that are causing your problems.

If it is all single valued, then I would try in <8> A;IF 9 = “3” THEN 4 ELSE “0”

The rest of it is in the sort list sentence to break-on 5 with a det-supp.

i.e. sort file by 1 by 5 break-on 1 break-on 5 total <new dict item> id-supp det-supp

--
--
IMPORTANT: T24/Globus posts are no longer accepted on this forum.
 
To post, send email to jB...@googlegroups.com
To unsubscribe, send email to jBASE-un...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/jBASE?hl=en

---
You received this message because you are subscribed to the Google Groups "jBASE" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jbase+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

---

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4335 / Virus Database: 3722/7169 - Release Date: 03/08/14

[thomas.r...@gmail.com]

Thank you for your response.  We are electronically pushed information on collection accounts from our client - such as the name of the debtor, address, etc. in a file called ACCOUNT.  ACCOUNT does not contain a history of payments, which is very important to us.  We are also pushed transaction information in a separate file called TRANS, described above.  TRANS contains payment information (along with a lot of irrelevant noise such as account adjustments and incremental interest), but those payments are split to different "buckets" across accounts.  For example, a $100 payment may be split $50 to principal, $25 to interest and $25 to collection costs.  I am trying to reassemble these split  transactions to identify the $100 payment using a dictionary item, by taking each account number and adding the actual payments (marked by a "3" in attribute 9) for each date together in a sum.  

My thought would be then to use the dictionary item to export and write a file of just the actual payments per date per account that I could access from ACCOUNT using a translate dictionary item.  But I am not really sure how I will solve the many-to-one problem (one account number may have a multitude of payments over time, making it difficult to make the account number the primary key for a translate lookup).

The long and the short of it is that I am trying to do something that is probably better left to a PICK BASIC program, but I haven't learned PICK well enough to do it yet.  

[Peter Mowatt]

I get it.

I assume that you have no control over the structure of these records. The structure is not built to lend itself to dictionary listing for the discreet output that you want.  

You could experiment using BY-EXP  in your sort-select statement to explode the multi values and then list with the filter.

i.e. SSELECT ACCOUNT BY-EXP 9 then LIST WITH TRNDATE = “MM/DD/YY” AND WITH 9 = “3’ . I haven’t used it on jBase but it could be worth a try.

If the situation were mine without the mv skills to program it but I know how to export it, then I would use simple attribute dictionaries and export the full list and use the power of Excel or an Access database to do the filtering for you. Build macros for one click formatting and filtering.

--

--
IMPORTANT: T24/Globus posts are no longer accepted on this forum.
 
To post, send email to jB...@googlegroups.com
To unsubscribe, send email to jBASE-un...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/jBASE?hl=en

---
You received this message because you are subscribed to the Google Groups "jBASE" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jbase+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

No virus found in this message.
Checked by AVG - www.avg.com

Version: 2014.0.4336 / Virus Database: 3722/7187 - Release Date: 03/12/14

[Mark Hogden]

Without seeing the entire dicts for both files, it's hard to say for sure, but on the face of it it sounds like the query should be on the TRANS file with translates back to the ACCOUNT file.

[Kevin Powick]

Thank you for your response.  We are electronically pushed information on collection accounts from our client - such as the name of the debtor, address, etc. in a file called ACCOUNT.  ACCOUNT does not contain a history of payments, which is very important to us.  We are also pushed transaction information in a separate file called TRANS, described above.  TRANS contains payment information (along with a lot of irrelevant noise such as account adjustments and incremental interest), but those payments are split to different "buckets" across accounts.  For example, a $100 payment may be split $50 to principal, $25 to interest and $25 to collection costs.  I am trying to reassemble these split  transactions to identify the $100 payment using a dictionary item, by taking each account number and adding the actual payments (marked by a "3" in attribute 9) for each date together in a sum.  

What you're asking for is actually pretty easy to get in a report format without any new dictionary items.  Try the following, correcting any errors I've made in the dict names I've used.

SORT TRANS WITH SOURCE = "3" BY ACCOUNT BY DATE BREAK-ON ACCOUNT BREAK-ON DATE TOTAL TRAN.AMT

That should give you a detailed list of TRANS items with Source = "3", sorted and subtotalled by Account and Date.

If you just want the totals without the detail, add the clause ID-SUPP to the end of the above query statement

If you need any information from the ACCOUNT file, such as client name, you can add the appropriate dictionary item to the TRANS file to pull the client name from the Account file.

e.g. Adding a dict item to TRANS to pull client name from ACCOUNT

Assuming the following:

In TRANS file, the Account # is in attribute # 1

In ACCOUNT file the Client name is in attribute # 2

01 S

02 01

03 Client Name

04

05

06

07 

08 TACCOUNT;X;02;02 

09 L

10 25

--

Kevin Powick

[Chris Mathews]

Kevin is correct.  To give you the right attributes:

SORT TRANS WITH 9 EQ "3" BY 1 BY 22.4 BREAK-ON 1 BREAK-ON 22.4 TOTAL SIGNED-TRANS-AMT DET-SUPP.

DICT items exist in TRANS already for attributes as the account name and client name.

[Kevin Powick]

On Thursday, 13 March 2014 12:05:24 UTC-4, Kevin Powick wrote:

 

If you just want the totals without the detail, add the clause ID-SUPP to the end of the above query statement

Correction:  I should have said use the clause DET-SUPP instead of ID-SUPP, if you only want totals without detail lines.  Sorry for the typo.

--

Kevin Powick