Reverse number sign in bulk

[MikeMV]

Hello,

I am using VFP 9 SP2, I have a table that contains the following fields:

stockcode, jan, feb, mar .... dec

The fields contain negative, zero and positive data, I need to reverse the sign before merging the data into another table, I am doing this now with a do while loop, but I am wondering if there is a way to accomplish this with a single statement.

I will greatly appreciate your feedback.

 

[Olaf Doschke]

Quite simiar to another problem you had:

update table1 from table2 set table1.jan = table1.jan-table2.jan, ... Where table2.stockcode = table1.staockcode

same for feb, mar, ... dec

You're asking for quite similar stuff now for a while, maybe you'd get more out of this, if you'd give us the bigger picture of your problems.

I have the feeling you are doing your aggregations the wrong way. It's simpler to use cross table wizard or pivot table wizard on the non-aggregated data, than to aggregate partial aggregated data.

Bye, Olaf.

 

[MikeMV]

Thanks Olaf,

I am sorry it appears that I am posting the same question in different ways, you and Borislav helped me with 2 other questions which was a big help, but although I am still working with the same data this is a different issue.

The basic problem is that I have to reverse the sign of the data in one of the tables, this is irrelevant of the second table where the data is going to be merged, but I may be wrong. I'll try to give you a brief idea of the bigger picture.

We sell lighting fixtures and I am trying to collect data of all the components taken out of inventory on a monthly basis, the components go out of inventory in 2 ways ....

A direct sale of let's say 100 lighting fixtures of stockcode 1234, this goes out as a positive number and stored in one table ... +100, sometimes the sale gets cancelled and the items go back into stock as a negative -100

We also use components in lighting "kits" those go out of inventory as a negative quantity and go into another table, let's say -100 pcs of XYZ, sometimes the kit gets "reversed" and put back into inventory as a positive number.

I know the above does not make a lot of sense in the real world, but the application we use at work is strange and we are not going to change it anytime soon.

I end up with 2 tables that look like this:

sales:

stockcode jan feb

1234 100 -50
4567 50

kits:

xyz -100 +50
4567 - 50

In the end I have to do a report of all components "sold" I need the data to be all in positive if it went out of inventory and negative if it came back in. That is why the jan field of the kit table has to be reversed to +100 and +50. If I add 4567 from both tables I would end up with 0 when in reality I need to show +100.

I hope I did not make this too confusing and I am sorry if I am missing something.

Right now I use a formula such as:

DO CASE
CASE jan < 0
REPLACE jan WITH ABS(jan)
CASE jan > 0
REPLACE jan WITH (jan-(jan*2))
ENDCASE

This works, but I have suspicion that it can be done much simpler without writing so much code.

Thanks again,

 

[mm0000]

replace all jan with jan * -1

 

[mm0000]

replace all jan with jan * -1

 

[MikeMV]

Thanks for the formula.

Olaf,

Maybe this will work and I can consolidate your previous solution, do you see a problem with this?

INSERT INTO Sales (field list here);
SELECT StockCode,;
SUM(JAN*-1) AS Jan,;
...
SUM(Dec*-1) AS Dec;
FROM (SELECT * FROM Sales1;
UNION ALL;
SELECT * FROM Sales2) Tbl1;
GROUP BY StockCode

 

[Olaf Doschke]

mcampos, this union will not work, as a union will not sum the values for each stockcode. I posted a solution, you simply don't add but subtract the values of your second table, no need to multiply by -1 and then add, subtraction does the same. Very basic and simple math. I'm not just negating the values, I'm already doing the aggregation of the two tables.

Ragarding your case statement, all you do is multiply by -1, if value<0, abs(value) is -value.

value-2*value = value-value-value = 0-value = -value, this would work for both positive and negative values. You're not a math ace, are you?

Bye, Olaf.

 

[Olaf Doschke]

sales:

stockcode jan feb

1234 100 -50
4567 50

kits:

xyz -100 +50
4567 - 50

In the end I have to do a report of all components "sold" I need the data to be all in positive if it went out of inventory and negative if it came back in. That is why the jan field of the kit table has to be reversed to +100 and +50. If I add 4567 from both tables I would end up with 0 when in reality I need to show +100.

Then do

Insert Into Finaltable (field list here);
Select;
(Sum(sales.jan) - Sum(kits.jan)) as jan, ;
(Sum(sales.feb) - Sum(kits.feb)) as feb, ; 
...
From sales, kits;
Where sales.stockcode = kits.stockcode
group by sales.stockcode

or if it's one record per stockcode in sales and on in kits, even simpler:

Insert Into Finaltable (field list here);
Select;
sales.jan - kits.jan as jan, ;
sales.feb - kits.feb as feb, ; 
...
From sales, kits;
Where sales.stockcode = kits.stockcode

Bye, Olaf.

 

[MikeMV]

Got it now Olaf, thank you.

"You're not a math ace, are you?"

Funny, actually in my younger years I was pretty sharp, aging has a nasty habit of dulling your senses and slowing down your brain, aging is a terrible thing to do. Fortunately I have a good bank of life/business experience which can still be useful to young people starting out.

Thanks again for your help and patience, your input is helping me streamline this program and will be used in future projects.