Programming Puzzle 6 - Another nested loop

[jeb richardson]

Re: Programming Puzzle 6 - Another nested loop

Tom, the first one I created puz6 was just like your script. However, I didn't want to settle for that. So I looked into a different approach to get the answer and with some help from Stan, the newpuz6 is a script that isn't like yours, but still delievers the same answer.

puz6 - 08-08-2011 -1.txt

NEWPUZ6 - 08-08-2011 -1.txt

[Tom Cone Jr]

Re: Programming Puzzle 6 - Another nested loop

Here's my solution.

The attachment is the "exported" script. In this format the script can be easily "imported" into your copy of the puzzle database.

-- tom

[aschone]

Re: Programming Puzzle 6 - Another nested loop

Key Functions
Word_Exists()
<array>.Find()
Arrays - Automatically Inserting Elements into an Array
<array>.Dump_Properties()

'Date Created: 09-Jun-2011 11:29:00 AM
'Last Updated: 09-Jun-2011 04:16:52 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 vFormatC as C

DIM vIndxN as N

DIM arrInvoice[0] as P
DIM arrInvoice[0].ProductC as C
DIM arrInvoice[0].InvoiceListC as C
DIM arrInvoice[0].InvoiceCountN as N

vInvoice_ItemsP = table.open("invoice_items",FILE_RW_SHARED)
	vOrderC = "Product_id"
	vInvoice_ItemsP.order(vOrderC)
	vInvoice_ItemsP.fetch_first()
	WHILE .not. vInvoice_ItemsP.fetch_eof()
		vIndxN = arrInvoice.find(vInvoice_ItemsP.Product_Id, "ProductC")
		IF vIndxN > 0 THEN
			'Product found
			IF word_exists(arrInvoice[vIndxN].InvoiceListC, vInvoice_ItemsP.invoice_nu,";") THEN
				'do nothing
			ELSE
				arrInvoice[vIndxN].InvoiceListC = arrInvoice[vIndxN].InvoiceListC + ";" + vInvoice_ItemsP.invoice_nu
				arrInvoice[vIndxN].InvoiceCountN = arrInvoice[vIndxN].InvoiceCountN + 1
			END IF
		ELSE	
			'Product not Found
			arrInvoice[].ProductC = vInvoice_ItemsP.product_id
			arrInvoice[..].InvoiceListC = vInvoice_ItemsP.invoice_nu
			arrInvoice[..].InvoiceCountN = 1
		END IF
		vInvoice_ItemsP.fetch_next()
	END WHILE
vInvoice_ItemsP.close()

vFormatC = "ProductC|InvoiceCountN|InvoiceListC"
trace.WriteLn(arrInvoice.dump_properties(vFormatC))





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

[Stan Mathews]

Re: Programming Puzzle 6 - Another nested loop

My eyes are getting old but I would swear that I see P012 on invoice 000021 twice. Once qty 3 and once qty 7.
Likewise P023 on invoice 000026, qtys 7 and 5.

[Tom Cone Jr]

Re: Programming Puzzle 6 - Another nested loop

Stan, my results agree with yours. I see now that it would be possible for the same item to be on an invoice more than once. My solution (to be revealed later) assumes that that does not happen, so for me, the count of invoice_item records for a given product is the same as the count of invoices that contained that product.

[Stan Mathews]

Re: Programming Puzzle 6 - Another nested loop

Not many answers yet. I'll attach mine as a text file so no one will see it unless they so desire.

Fixed the test of having duplicate listing of the same item on one invoice.

[Tom Cone Jr]

Beginning Xbasic Programming

Puzzle 6 - Another Nested loop

Your sales manager gives you the list of sold items found in the
Invoice_items table. (see attachment).

She has noticed that several products appear on multiple invoices.

She wants to quantify this, and has asked you to write an xbasic script
that will list each product and the number of separate invoices
upon which each appears.

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) your output should be directed to the trace window

d) your list should be in ascending order based on the Product_id field