Programming Puzzle 8 - Sort of a Sort

[Tom Cone Jr]

Date Created: 10-Jun-2011 10:59:01 AM
Last Updated: 10-Jun-2011 10:59:01 AM
Created By : Tom
Updated By : Tom
Beginning Xbasic Programming

Puzzle 8 - Sort of a Sort

You solved Puzzle_6, and then Puzzle 7, but your sales manager now tells you
that the list of product sales shouldn't have been sorted by the product_id.
She needs the list of sales by product sorted (descending) by the sales amount.
She wants the product that generated the most dollars first, and the product
that generated the fewest dollars last. She doesn't think this will
be very difficult for you because you've already computed the total dollars
for each product. Can you have the script done by lunch time?

Hah! Just like a manager, right? [ Doggone managers. What do they know, anyway? ]

Your job now is to write an xbasic script that will list each product
and for each product show the total dollars generated by that item
across all the invoices. But display the list in descending order based
on total dollars generated by each item.

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

[Lenny Forziati]

Can you have the script done by lunch time?

Hah! Just like a manager, right? [ Doggone managers. What do they know, anyway? ]

Tom, a good manager would know you are an Xbasic wiz and wait patiently in your office while you whip this up in 30 seconds since it's such a simple change. Why wait until lunch time?

[aschone]

I like this manager, gonna keep me in business for a long time.

[Tom Cone Jr]

Andy, that's sweet.

Because you're using a property array to hold the computed sums, displaying the list in sorted order becomes very easy.

In my nested loop approach, on the contrary, I have more work to do. Will post results later on.

-- tom

ps. Your contribution to this exercise will be more valuable if you include comments explaining
what your script is doing.

[Stan Mathews]

Don't laugh, Al.

Puzzle 8 kludge.txt

[Al Buchholz]

Don't laugh, Al.

Who?? me??

<Where's my Alfred E Neuman icon???>

[Tom Cone Jr]

Here's my solution.

-- tom

[jeb richardson]

Here's my attempt for puzzle 8.

Puz8.txt

[Mike Wilson]

Jeb,
Are you and Stan Mathews the same guy?

[jeb richardson]

No, I just started working under Stan. He's teaching me all about Alpha and how we (Houchens) use it. So in a nut shell I'm learning what he does and how he does it!

-Jeb

[Tom Cone Jr]

Jeb,

Thanks for taking a crack at it.

The upper bound of your loop is a table record number. This number is found by fetching the last record after the table opens. However, after finding that record number you re-order the table basedon the product_id. Be advised that this could change which record is "Last". A better approach would be to order the records first, then fetch the "last" in that sort sequence. i.e. the last "product_id", not the last record number. Instead of using a For ... Next loop based on incrementing physical record numbers, I believe it's better to use a while ... end while loop based on product_id's.

Does this make sense?

-- tom

[jeb richardson]

Yes. I only tried using the for...next to switch things up. But now i see your point. However, i was using my "last" variable just to get a count of records in that table. That way i could use the "last" variable in my loop. I used item.recno() and it returned 129 i believe. And i used this to allow my loop to run from 1-129("last"). Thanks, Tom!

-Jeb

[Tom Cone Jr]

Right.

But when you change the sort sequence you do not change the physical record numbers. The new "last" record may have a different "record number". The "last" record by sort sequence is not the same as the "last" physical record in the table. And, they will often ( if not usually ) have different "record numbers". The <tbl>.recno() method is looking at physical record numbers, not logical record positions in the current sort sequence.

[aschone]

I avoid using record numbers in code like the plague. You can put the table in record number order, fetch the last record and return record number 200. However the table only has 2 records in it. This can happen when records are deleted but the table is not packed. When the table is packed the records are renumbered. So in this example once you pack the table the last record will report a record number of 2.

Retrieving the record count is the appropriate method to get the correct total of un-deleted records in a table.