Replace Command

[drpep]

I have a database that is (example) arranged as follows:
Account Category Month YTD
A WageCosts 1000 4000
A OtherCosts 1500 6000
A ProdCosts 2000 8000 A Statistic 2500 10000 A CostperStat

I'm seeking a global command to replace CostperSales with (WageCosts+OtherCosts+ProdCosts)/Statistic per each Account or a short program. Thanks in advance

 

[Neil Toulouse]

LOCAL lnWage, lnOther, lnProd, lnStat

SELECT DISTINCT Account FROM MyTable INTO CURSOR curAcc

SELECT curAcc
SCAN

SELECT MyTable
LOCATE FOR ALLTRIM(UPPER(Account)) == ALLTRIM(UPPER(curAcc.Account)) AND ALLTRIM(UPPER(Category)) == 'WAGECOSTS'

IF FOUND()
lnWage = MyTable.Month && or YTD?
ELSE
lnWage = 0
ENDIF

SELECT MyTable
LOCATE FOR ALLTRIM(UPPER(Account)) == ALLTRIM(UPPER(curAcc.Account)) AND ALLTRIM(UPPER(Category)) == 'OTHERCOSTS'

IF FOUND()
lnOther = MyTable.Month && or YTD?
ELSE
lnOther = 0
ENDIF

SELECT MyTable
LOCATE FOR ALLTRIM(UPPER(Account)) == ALLTRIM(UPPER(curAcc.Account)) AND ALLTRIM(UPPER(Category)) == 'PRODCOSTS'

IF FOUND()
lnProd = MyTable.Month && or YTD?
ELSE
lnProd = 0
ENDIF

SELECT MyTable
LOCATE FOR ALLTRIM(UPPER(Account)) == ALLTRIM(UPPER(curAcc.Account)) AND ALLTRIM(UPPER(Category)) == 'STATISTIC'

IF FOUND()
lnStat = MyTable.Month && or YTD?
ELSE
lnStat = 0
ENDIF

UPDATE MyTable SET Month = (lnWage+lnOther+lnProd)/lnStat WHERE ALLTRIM(UPPER(Account)) == ALLTRIM(UPPER(curAcc.Account)) AND ALLTRIM(UPPER(Category)) == 'COSTPERSTAT'

SELECT curAcc
ENDSCAN


May sure you run this on test data before applying to 'live' data!!

HTH
Neil "I like work. It fascinates me. I can sit and look at it for hours..."

 

[cjulio]

select Account, iif (Category = "WageCosts", Month, 0) as WageCosts, iif (Category = "OtherCosts", Month, 0) as OtherCosts into array tmpCostperSales group by Account

for xpto = 1 to alen (tmpCostperSales, 1)
replace CostperSales with tmpCostperSales(xpto,2)+mpCostperSales(xpto,3) for Account = tmpCostperSales(xpto,1)
endfor

 

[Steve Bowman]

replace all CostperSales with ;
((WageCosts+OtherCosts+ProdCosts)/Statistic) ;
for alltrim(Account)=="AccountData"

This will roll through the table replacing based on the for clause.
Steve Bowman
steve.bowman@ultraex.com