TO MAKE PRG WITH TOTALS AND CASE 3

[Bondjames]

In ATTACHED table cmontds
I require sum of quantity of itemcode=”1”,”2”,”4”
Say in the giving table it will be 5000+321000+4000=330000 (SUMMS)
And sum of quantity of itemcode=”4”
Which will be in the given table as 302000 (SUMHSD)
NOW
SUMTOTQTY = SUM(SUMMS+SUMHSD)
now do case starts................

as per attached word

will be highly obliged if someone can give the prg to be written so that i can use and try to understand

thanks

regards

 

[mjcmkrsr]

Hi 007,

Could you please post the code you tried to achieve your goal.

hth

MarK

 

[Mike Lewis]

I'm afraid your post doesn't make much sense (at least, not to me). It's not clear exactly what you are trying to achieve. It doesn't help that you expect us to download a zip file to understand the problem.

Please try to explain the problem in simple language.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

And please don't SCREAM...!

 

[Bondjames]

Tore Bleken sorry Sir did not mean to just in the flow it was added

sir mjcmkrsr
sir Mike Lewis
attached herewith is screenshots of goals to be achieved from the table
first please see stepone required
and then steptwo required
please the same has to be grouped according to monthno into a table

forgive me if i could not explain the same in a better way earlier

please help
thanks
regards

 

[german12]

As far as I can understand you want always filter some defined items from your file and summarize their quantity.
(Step 1 in your goal)

You can test or code it like that:

SUM(quantity) to group1 for itemcode = 1 or itemcode = 2 or itemcode = 6
? group1 ***This is the 330000 in your sample.
SUM (quantity) TO group2 FOR itemcode = 4
? group2 *** this is the 302000 in your sample
SUM quantity TO grouptotal
? ? grouptotal *** this is your Sumtotoy in your sample.
? grouptotal
groupleft = grouptotal- group1-group2 * This is the difference for all other items which where not summarized.
? groupleft

Regards
Klaus


Peace worldwide - it starts here...

 

[Chris Miller]

Just some food for thought:

Create Cursor sales (itemcode char(2), quantity currency)
Insert into sales values ("3",  $999.95)
Insert into sales values ("15", $573.49)
Insert into sales values ("32",  $12.99)
Insert into sales values ("15", $219.90)
Insert into sales values ("32",  $25.98)
Insert into sales values ("3",  $199.95)

clear
Set Console on
Sum to variable for itemcode="3"
Sum to variable for itemcode="3 "

Only the second sum command gives the expected result. Why?

Select itemcode, sum(quantity) as total From sales group by itemcode

That SQL work and gives you totals for all itemcodes. It has no surprises of mis-grouping data. It can also be limited to only certain itemcodes by a WHERE clause, for example.

Chriss

 

[german12]

Chriss
This is interesting that when summing values, VFP obviously only recognizes the 1st letter of a string (itemcode is a string) when assigning groups. I think I would have fallen for that. I haven't found an exact explanation as to why this is the case.
But it stands out when I do this:
x="3"
y = "3 "
? VAL(x)
? VAL
In the display, the value of y is shifted one place to the right. This is also the case with string addition.
3.00 (for x)
. 3.00 (for y)

This is also the case with string addition.
x + y would result in "33" here.
But there are certainly better explanations.
In any case, the example is a good warning of how easy it is to mismap something when working with strings.
Thanks for the hint.
It's definitely safer to order with the value of strings.


Klaus


Peace worldwide - it starts here...

 

[Chris Miller]

Hi Klaus,

the answer to why is in the help topics about SET EXACT and SET ANSI ON/OFF or more generally the way VFP compares strings and evaluates them as equal or different. At default VFP only compares partial strings, so that "32"="3" is true, but at the same time it's not transient (you cannot swap and get the same boolean result), "3"="32" is false.

But even without knowing these details, there is a simple way to see why group by does not fail: The group by is done by the whole field, while code like SUM FOR itemcode="3" a) doesn't group for all itemcodes but just picks out the sum for itemcode "3" and b) isn't the full length itemcode "3 ". Whereas group by taking the full field length it groups by "3 " and "32" separately and those strings definitely differ no matter how strings are compared when they have different lengths, all strings in char fields by definition have the same length and when the padding spaces are taken into account, you don't misgroup.

That's why SUM ... FOR itemcode="3 " then works. Or whatever length itemcode has.

You can, indeed, reintroduce the same problem into SQL when you add a where clause that compares with a partial itemcode.

Select itemcode, sum(quantity) as total From sales group by itemcode where itemcode="3"

Doing that you also get the sums for both itemcode "3" and "32", but SQL saves your back, if you look into the result:

The where clause also fetches both itmecodes, so you get more than you expect, but group by makes two groups from the data and you still get the group you actually wanted and you can understand your comparison isn't as specifically picking out only one itemcode but all itemcodes starting with "3".

And the essence of it all, only use constant length identifiers when using strings. And, well, you could also say better not use strings for identifiers, but numbers. Comparison of numbers is more straight forward for what is equal, though floating point also has many gotchas in that respect.

The reason string comparisons work that weirdly also is easy to explain, as VFP is mainly for data processing and within databases before the introduction of variable length fields ou still wanted to find some Mr. Smith by writing WHERE lastname="Smith" and not WHERE lastname = PADR("Smith", len(lastname)) or even worse manually padding with spaces which would be code that only works as long as the field has the same length.

Think alone of the fact that SQL was meant to be nearer to natural language than usual code. Then it would sabotage that purpose if the user of SQL has to think about such details in the queries. VFP by default differs from how the ANSI standardization of SQL defined string comparisons, with SET ANSI ON you can also use the "too simple" where clause and only get one group. Same SQL, just after SET ANSI ON:

Is it therefore recommended to SET ANSI ON? It depends. But there are good reasons for VFP to differ from the ANSI standard in that respect. So it's not a bugfix that you can turn on or off, it's a design decision that goes into aspects of how the indexes are organized and rushmore optimization, I guess, too.

Chriss

 

[Bondjames]

thank you sirs i added for itemcode="1 " and "2 " got my result
thanks again
regards