Alpha DevCon 2018
Results 1 to 11 of 11

Thread: Programming Puzzle 5 - Nested loops

  1. #1
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,299

    Default Programming Puzzle 5 - Nested loops

    Alright boys and girls, the preliminaries are over. Let's turn our attention
    to some actual data.



    Puzzles and Problems

    Beginning Xbasic Programming

    Puzzle 5 - Nested loops

    I've attached a sample database, containing the invoice_items table from AlphaSports, and an index (CDX) file.

    Write an xbasic script that opens this table, arranges its records in ascending order based
    on the "invoice_nu" field, and then computes the total for each invoice by summing
    the "extension" fields for all records that have the same invoice number. For each invoice
    write the invoice number, the number of different items on the invoice (not the item count,
    but the number of "lines" on the invoice), and the invoice total to the trace window. At
    the end, write the grand total of all the invoices to the trace window.

    Instructions:

    a) you may not use any of the built in summary functions, methods, or operations in Alpha Five. We're doing this one by hand.

    b) you may open the table only once

    c) you may not use any queries or filters



    NOTE: These puzzles and exercises will be studied by beginning programmers. Supply
    comments throughout your script to explain what your script is doing and why. You're
    writing for posterity. Be thorough. Thanks.

    Postscript: I've changed the attachment. It's now a complete database. Unzip the attachment and extract contents to an empty folder. This was necessary because the original attachment did not include supporting dictionary files that would have been necessary. Sorry for the confusion. -- tom
    Last edited by Tom Cone Jr; 06-07-2011 at 09:44 AM. Reason: Replace attachment

  2. #2
    "Certified" Alphaholic
    Real Name
    Andrew Schone
    Join Date
    Dec 2005
    Location
    Kansas
    Posts
    1,044

    Default Re: Programming Puzzle 5 - Nested loops

    Very good puzzle. No, there are no comments; I very seldom, if ever, put comments in my code.

    This code is used in our main database on a daily basis as part of a payment posting function. I have to summarize the claim totals and subtract the amount already paid to display a current amount due.

    Code:
    'Date Created: 07-Jun-2011 11:33:30 AM
    'Last Updated: 07-Jun-2011 05:10:51 PM
    'Created By  : andy
    'Updated By  : andy
    OPTION STRICT
    OPTION ENCRYPTED_TOKENS
    ON ERROR GOTO ERR_HANDLER
    
    DIM vInvoice_ItemsP as P
    
    DIM vOrderC as C
    DIM vFilterC as C
    
    DIM vIndxN as N
    DIM vGrandTotalN as N
    
    DIM arrInvoice[0] as P
    DIM arrInvoice[0].InvoiceC as C
    DIM arrInvoice[0].TotalN as N
    DIM arrInvoice[0].LinesN as N
    
    vInvoice_ItemsP = table.open("invoice_items",FILE_RW_SHARED)
    	vOrderC = "invoice_nu"
    	vInvoice_ItemsP.order(vOrderC)
    	vInvoice_ItemsP.fetch_first()
    	WHILE .not. vInvoice_ItemsP.fetch_eof()
    		vIndxN = arrInvoice.find(vInvoice_ItemsP.invoice_nu, "InvoiceC")
    		IF vIndxN > 0 THEN
    			arrInvoice[vIndxN].TotalN = arrInvoice[vIndxN].TotalN + vInvoice_ItemsP.extension
    			arrInvoice[vIndxN].LinesN = arrInvoice[vIndxN].LinesN + 1
    		ELSE
    			arrInvoice[].InvoiceC = vInvoice_ItemsP.invoice_nu
    			arrInvoice[..].TotalN = vInvoice_ItemsP.extension
    			arrInvoice[..].LinesN = 1
    		END IF
    		vInvoice_ItemsP.fetch_next()
    	END WHILE
    vInvoice_ItemsP.close()
    
    FOR vIndxN = 1 to arrInvoice.size()
    	trace.WriteLn( "Invoice: " + arrInvoice[vIndxN].InvoiceC + " Total: " + arrInvoice[vIndxN].TotalN + " Lines: " + arrInvoice[vIndxN].LinesN)
    	vGrandTotalN = vGrandTotalN + arrInvoice[vIndxN].TotalN
    NEXT
    
    trace.WriteLn("Grand Total: " + vGrandTotalN)
    
    
    END
    '-----------------------------------
    'Error handler
    '-----------------------------------
    ERR_HANDLER:
    	Dim err as N= error_code_get()
    	Dim msg as C= "Error # "+ err + crlf() + error_text_get(err)
    	msg = msg + crlf() + "Script: " + error_script_get()
    	msg = msg + crlf() + "Line #: " + error_line_number_get()
    
    	trace.writeln(msg)
    
    	ui_msg_box("Error", msg, ui_attention_symbol)
    	msg = ""
    	
    	END
    Results:
    Code:
    Invoice: 000001 Total: 949.29 Lines: 6
    Invoice: 000002 Total: 271.61 Lines: 2
    Invoice: 000003 Total: 2629.34 Lines: 6
    Invoice: 000004 Total: 987.17 Lines: 4
    Invoice: 000005 Total: 306.9 Lines: 4
    Invoice: 000006 Total: 184.84 Lines: 4
    Invoice: 000007 Total: 1245.83 Lines: 4
    Invoice: 000008 Total: 240.2 Lines: 2
    Invoice: 000009 Total: 107.1 Lines: 1
    Invoice: 000010 Total: 59.38 Lines: 3
    Invoice: 000011 Total: 194.15 Lines: 4
    Invoice: 000012 Total: 22.33 Lines: 2
    Invoice: 000013 Total: 210.64 Lines: 4
    Invoice: 000014 Total: 270.77 Lines: 3
    Invoice: 000015 Total: 155.35 Lines: 3
    Invoice: 000016 Total: 56.42 Lines: 2
    Invoice: 000017 Total: 100.08 Lines: 2
    Invoice: 000018 Total: 640.06 Lines: 5
    Invoice: 000019 Total: 329.8 Lines: 3
    Invoice: 000020 Total: 367.54 Lines: 3
    Invoice: 000021 Total: 422.36 Lines: 5
    Invoice: 000022 Total: 11.76 Lines: 2
    Invoice: 000023 Total: 45.9 Lines: 1
    Invoice: 000024 Total: 897.76 Lines: 3
    Invoice: 000025 Total: 602.35 Lines: 4
    Invoice: 000026 Total: 910.31 Lines: 6
    Invoice: 000027 Total: 343.71 Lines: 7
    Invoice: 000028 Total: 623.78 Lines: 11
    Invoice: 000029 Total: 400.52 Lines: 2
    Invoice: 000030 Total: 506.5 Lines: 4
    Invoice: 000031 Total: 11.77 Lines: 1
    Invoice: 000034 Total: 950.28 Lines: 6
    Invoice: 000035 Total: 950.28 Lines: 6
    Invoice: 000036 Total: 506.5 Lines: 4
    Grand Total: 16512.58
    Last edited by aschone; 06-08-2011 at 09:34 AM.

  3. #3
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,299

    Default Re: Programming Puzzle 5 - Nested loops

    Andrew, thanks for the response. I was beginning to think maybe the exercise was too difficult for anyone to attempt.

    Your approach was not expected. You use several advanced features of the xbasic language and managed to produce a solution that does not use a nested loop. Darn !

    For the benefit of other readers, here's a summary of your approach (let me know if I've misunderstood something):

    a) At line 17 - 20 you declare an array of pointers ( a "Property" array), containing zero elements

    b) At line 22 - 25 you open the table, and then "order" the table (ascending) by invoice_nu, then you move the record pointer to the top of the sorted list. [ Our fearless scorekeeper, Ignatious A. Pickypicky, thinks you broke the rules of the puzzle here. The tbl.order() method is a form of query, in his esteemed opinion. He would have preferred it if you had simply set the Inv_num index primary, since it already contains a sorted list of index keys. ]

    c) In the code block from line 26 to 37 things get very interesting. Basically, you fetch through the sorted list of records one at a time. After each fetch your script tries to find the current invoice_nu field value in the array. If an element is present that corresponds to the current invoice_nu field value the current extension field value is added to a running total entry in your array (TotalN), and the line counter element in the array is incremented. Otherwise, a new element is added to the array, and the extension field value is stored there, and the line counter starts over at 1.

    Very clever, indeed.

    I'll post my own solution now. You may be surprised at the differences in my approach.

    Thanks again for a useful contribution to this thread.

  4. #4
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,299

    Default Re: Programming Puzzle 5 - Nested loops

    Here's my solution. It employs a nested loop. The records are arranged in index order, based on the invoice_nu field. This groups all items records for the same invoice together. The code then loops through all the records. When the script sees that the current invoice_nu has changed it "knows" that the last item for the previous invoice has been processed. It then loops through the next batch of records until the invoice_nu field value changes again.

    Code:
    'Date Created: 07-Jun-2011 09:40:06 AM
    'Last Updated: 08-Jun-2011 06:02:59 AM
    'Created By  : Tom
    'Updated By  : Tom Cone Jr
    'Puzzles and Problems
    '
    'Beginning Xbasic Programming
    '
    'Puzzle 5 - Nested loops 
    '
    'An "invoice_items" table and its index file are attached. These came from the AlphaSports sample that ships with Alpha Five.
    '
    'Write an xbasic script that opens this table, arranges its records in ascending order based
    'on the "invoice_number" field, and then computes the total for each invoice by summing
    'the "extension" fields for all records that have the same invoice number. For each invoice
    'write the invoice number, the number of different items on the invoice (not the item count, 
    'but the number of "lines" on the invoice), and the invoice total to the trace window. At
    'the end, write the grand total of all the invoices to the trace window.
    '
    'Instructions:
    '
    'a) you may not use any of the built in summary functions, methods, or operations in Alpha Five. We're doing this one by hand.
    '
    'b) you may open the table only once
    '
    'c) you may not use any queries or filters 
    '
    '
    
    ' Begin by declaring and initializng variables
    dim Items as P 			' will point to the invoice_items table
    dim vc_curr_inv as C = ""		' will hold the current invoice number
    dim vn_inv_tot as N = 0		' will hold be used to accumulate the invoice total
    dim vn_line_count as N = 0		' will hold the line count for each invoice
    dim vn_grand_tot as N = 0		' will hold the grand total
    
    ' Now open the table and set the desired sort sequence
    Items = table.open("invoice_items")	'Note: script assumes table will open
    idx = Items.index_primary_put("Inv_Num")  	'Invoice_nu, ascending, all
    				'NOTE:  this groups all invoice items together
    				'       that have the same invoice_nu field values
    
    Items.fetch_first()			' start at the top of the table
    
    while .not. items.fetch_eof()	' loop through all the records - loop ends when script
    			' tries to fetch past the end of file
    	vn_inv_tot = 0	' clear the accumulator for invoice total
    	vn_line_count = 0	' clear the accumulator for the line count
    	vc_curr_inv = Items.invoice_nu    'get the current invoice number
    	
    	'now fetch through the sorted list of invoice items records, accumulating
    	'the invoice total; but stop when we fetch past end of file, or when then
    	'invoice_nu no longer matches the invoice number being summarized.
    	
    	while .not. items.fetch_eof() .and. vc_curr_inv = items.invoice_nu
    		vn_inv_tot = vn_inv_tot + items.extension	' add current extension to invoice total
    		vn_line_count = vn_line_count + 1	' increment the line counter
                                    ' add current extension value to grand total
    		vn_grand_tot = vn_grand_tot + items.extension		
                                    items.fetch_next()
    	end while
    	
    	'on exit from inner loop vn_inv_tot holds the invoice total
    	'so now we write it to the trace window
    	
    	trace.writeln("Invoice number " + vc_curr_inv + " has " + str(vn_line_count,3) + " lines" +\
    	", and totals: " + str(vn_inv_tot,10,2))
    	
    	' now loop back to top of outer loop, and process next batch of items records	
    	
    end while	
    
    ' on exit of the outer loop vn_grand_tot holds the total of all invoices
    trace.writeln("")
    trace.writeln("Grand total of all invoices: " + ltrim(str(vn_grand_tot,12,2)))
    
    ' cleanup on the way out
    ' remember to close the open table
    
    items.close()
    
    end
    Producing this in the trace window:
    Code:
    Invoice number 000001 has   6 lines, and totals:     949.29
    Invoice number 000002 has   2 lines, and totals:     271.61
    Invoice number 000003 has   6 lines, and totals:    2629.34
    Invoice number 000004 has   4 lines, and totals:     987.17
    Invoice number 000005 has   4 lines, and totals:     306.90
    Invoice number 000006 has   4 lines, and totals:     184.84
    Invoice number 000007 has   4 lines, and totals:    1245.83
    Invoice number 000008 has   2 lines, and totals:     240.20
    Invoice number 000009 has   1 lines, and totals:     107.10
    Invoice number 000010 has   3 lines, and totals:      59.38
    Invoice number 000011 has   4 lines, and totals:     194.15
    Invoice number 000012 has   2 lines, and totals:      22.33
    Invoice number 000013 has   4 lines, and totals:     210.64
    Invoice number 000014 has   3 lines, and totals:     270.77
    Invoice number 000015 has   3 lines, and totals:     155.35
    Invoice number 000016 has   2 lines, and totals:      56.42
    Invoice number 000017 has   2 lines, and totals:     100.08
    Invoice number 000018 has   5 lines, and totals:     640.06
    Invoice number 000019 has   3 lines, and totals:     329.80
    Invoice number 000020 has   3 lines, and totals:     367.54
    Invoice number 000021 has   5 lines, and totals:     422.36
    Invoice number 000022 has   2 lines, and totals:      11.76
    Invoice number 000023 has   1 lines, and totals:      45.90
    Invoice number 000024 has   3 lines, and totals:     897.76
    Invoice number 000025 has   4 lines, and totals:     602.35
    Invoice number 000026 has   6 lines, and totals:     910.31
    Invoice number 000027 has   7 lines, and totals:     343.71
    Invoice number 000028 has  11 lines, and totals:     623.78
    Invoice number 000029 has   2 lines, and totals:     400.52
    Invoice number 000030 has   4 lines, and totals:     506.50
    Invoice number 000031 has   1 lines, and totals:      11.77
    Invoice number 000034 has   6 lines, and totals:     950.28
    Invoice number 000035 has   6 lines, and totals:     950.28
    Invoice number 000036 has   4 lines, and totals:     506.50
    
    Grand total of all invoices: 16512.58
    Last edited by Tom Cone Jr; 06-08-2011 at 06:49 AM. Reason: clean up line breaks

  5. #5
    "Certified" Alphaholic
    Real Name
    Andrew Schone
    Join Date
    Dec 2005
    Location
    Kansas
    Posts
    1,044

    Default Re: Programming Puzzle 5 - Nested loops

    Quote Originally Posted by Tom Cone Jr View Post
    Our fearless scorekeeper, Ignatious A. Pickypicky, thinks you broke the rules of the puzzle here. The tbl.order() method is a form of query, in his esteemed opinion. He would have preferred it if you had simply set the Inv_num index primary, since it already contains a sorted list of index keys.
    Mr. PickyPicky I was sure I put an apple on your desk when I walked into class today, did that not buy me a little leeway?

    I recently started to use <tbl>.order() in lieu of (query.filter and query.order) and <TBL>.INDEX_PRIMARY_PUT(). Index_Primary_put() can make reading the code more difficult in that you must also have the index definitions available in order to know what a particular index being applied to a table will do. Query.filter and query.order require additional lines of code to establish the settings, apply the settings, and then blank out the settings for fear that a previous query settings be incorrectly applied the next time you do a query. I find these additional lines of code muddy up the script making it more cumbersome to read than is necessary.

    <tbl>.order has the benefit of using a predefined index if it matches the settings or if no index file is present it can still operate. In Puzzle 5, the data file was originally uploaded without the index file (in all honesty I downloaded the puzzle after you added the index file). In this case since an index already existed that matched my order description <tbl>.order() used the pre-existing index.
    Code:
    vInvoice_ItemsP = table.open("invoice_items",FILE_RW_SHARED)
    vOrderC = "invoice_nu"
    
    vIndxP = vInvoice_ItemsP.order(vOrderC)
    ?vIndxP.type_get()
    = 2
    http://support.alphasoftware.com/alp..._GET%28%29.htm
    Code:
    The <INDEX>.TYPE_GET() method returns the type of the index referenced by the <INDEX> object pointer. The index type can be: 
    2 = a tag in an index file
    6 = a query list
    -1 = record number order
    http://support.alphasoftware.com/alp...RDER%28%29.htm
    Code:
    Description
    
    The <TBL>.ORDER() method orders, and optionally filters, data from the table referenced by <TBL>.
    
    An object pointer, <INDEX>, is returned. <INDEX> will reference either:
    
        an index that matches Order_Expression and Filter_Expression
    
        if no index matches, a new query list.
    
    Use this method as an alternative to <TBL>.INDEX_PRIMARY_PUT() when you want to specify order and filter expressions, rather than index tag names.
    
    An advantage of this method over <TBL>.INDEX_PRIMARY_PUT()is that <TBL>.INDEX_PRIMARY_PUT()could fail if the data dictionary file for a table is missing. The data dictionary contains the long index tag names.
    
    In addition to using an index if one is available, <TBL>.ORDER()does not use the global query variables ( query.filter, query.order, etc.).

  6. #6
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,299

    Default Re: Programming Puzzle 5 - Nested loops

    Thanks again, Andrew. Very helpful!

  7. #7
    Member
    Real Name
    Jeb Richardson
    Join Date
    Aug 2011
    Location
    Bowling Green, KY
    Posts
    26

    Default Re: Programming Puzzle 5 - Nested loops

    This is my attempt for Puzzle #5.

    puz 5.txt

  8. #8
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,299

    Default Re: Programming Puzzle 5 - Nested loops

    Jeb, Prof. Pickypicky has been having trouble with your offering.

    1) not all the invoices are showing up in the trace window

    2) the "line count" for each invoice is wrong for all but the first invoice

    3) your script opens a table, but does not close it when the script has finished its work. This is a dangerous practice. Always best to leave the user's system exactly like you found it. So cleanup on the way out is the Professor's motto.

    Care to take another crack at the problem?

    -- tom

  9. #9
    Member
    Real Name
    Jeb Richardson
    Join Date
    Aug 2011
    Location
    Bowling Green, KY
    Posts
    26

    Default Re: Programming Puzzle 5 - Nested loops

    Okay I went back and looked at my script. Here is my revised script!

    puz5.txt

  10. #10
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,299

    Default Re: Programming Puzzle 5 - Nested loops

    Jeb, that's much better.

    Still have an issue with the last line.

    table.close()

    tells Alpha to close an open instance of a table for which a pointer has previously been created. That pointer is named "table". Since that pointer is never created in your script this statement is wrong. the <tbl>.close() method should be run against the object pointer you created when you opened the table. Clear as mud? Holler if you have questions or if this explanation is as inadequate as it feels.

  11. #11
    Member
    Real Name
    Jeb Richardson
    Join Date
    Aug 2011
    Location
    Bowling Green, KY
    Posts
    26

    Default Re: Programming Puzzle 5 - Nested loops

    Yes I understand. It slipped my mind that the table pointer was item. So in that case my last line should read item.close() instead of table.close() .

Similar Threads

  1. Programming Puzzle 2 - Another Simple Loop
    By Tom Cone Jr in forum Xbasic Programming Puzzles
    Replies: 16
    Last Post: 12-20-2014, 10:38 AM
  2. Programming Puzzle 1 - Simple Loop
    By Tom Cone Jr in forum Xbasic Programming Puzzles
    Replies: 32
    Last Post: 12-26-2011, 01:38 PM
  3. Programming Puzzle 3 - Fractions, anyone?
    By Tom Cone Jr in forum Xbasic Programming Puzzles
    Replies: 7
    Last Post: 11-08-2011, 05:40 AM
  4. Programming Puzzle 4 - A bug riddled riddle
    By Tom Cone Jr in forum Xbasic Programming Puzzles
    Replies: 18
    Last Post: 08-05-2011, 06:15 AM
  5. for each...next loops
    By StephenP in forum Application Server Version 9 - Web/Browser Applications
    Replies: 1
    Last Post: 03-04-2009, 03:01 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •