Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The problem is that AccountingPrice (accounting price for 1 unit) is stored in one table (Products) and SalesPrice in another (Sales)
I calculate Margins in tables and graphs as
Maring=sum(SalesPrice-AccountingPrice*Amount)
But is slows down application, so the best way as I see it to calculate Margin as a field in one of the tables, of create another table and
joing it to one of them.. resident ?
Example:
Sales:
LOAD iddoc as idjournal,
sp283 as IdProduct,
sp285 as Pricefor1,
sp284 as Amount,
//sp285*sp284 as PricelistPrice (why it doesn't work??)
sp286 as SalesPrice,
sp287 as tax,
(sp286+sp287) as Sales;
SQL SELECT *
FROM `DT294`
where sp285>0;
Products:
LOAD id as IdProduct,
descr as Product,
sp141 as AccountigPrice ;
SQL SELECT *
FROM `sc156` where isfolder=2;
and why doesn't work?:
Let Maring=sum(SalesPrice-AccountingPrice*Amount)
So how to create a field from fields of 2 different tables into one?
Here it the test script
/*******This table is just for data*******/
salesData:
Load * INLINE
[sp283,sp285, sp284, sp286
PR1 ,1,4,20
PR2 ,3,5,25
];
Sales:
LOAD
sp283 as IdProduct,
sp285 as Pricefor1,
sp284 as Amount,
sp285*sp284 as PricelistPrice ,
sp286 as SalesPrice
RESIDENT salesData;
/********this table is for data only *******/
ProductsData:
Load * INLINE
[id , descr, sp141
PR1,Product 1, 15
PR2, product 2, 10
];
Product:
LOAD id as IdProduct,
descr as Product,
sp141 as AccountigPrice
RESIDENT ProductsData;
Left Join(Sales)
LOAD
IdProduct,
AccountigPrice
RESIDENT Product;
SalesAggr:
Load
IdProduct,
sum((SalesPrice-AccountigPrice)*Amount) as Margin
RESIDENT Sales
GROUP BY IdProduct;
Hi,
You should be able to do it in the chart itself, If not you can load in the script also,
I just put together small QVW for you, Please look in to it.
I Hope that help.
There is no problem in charts,but in brings more calculations to the application,
so I want to find the way to creaty new field margin in the script..
please make qvw openable in PersonalEdition..
Instead of this:
Alexadner Gonchar wrote:Sales:
LOAD iddoc as idjournal,
sp283 as IdProduct,
sp285 as Pricefor1,
sp284 as Amount,
//sp285*sp284 as PricelistPrice (why it doesn't work??)
sp286 as SalesPrice,
sp287 as tax,
(sp286+sp287) as Sales;
SQL SELECT *
FROM `DT294`
where sp285>0;
Products:
LOAD id as IdProduct,
descr as Product,
sp141 as AccountigPrice ;
SQL SELECT *
FROM `sc156` where isfolder=2;
Do this:
Sales:
LOAD iddoc as idjournal,
sp283 as IdProduct,
sp285 as Pricefor1,
sp284 as Amount,
//sp285*sp284 as PricelistPrice (why it doesn't work??)
sp286 as SalesPrice,
sp287 as tax,
(sp286+sp287) as Sales;
SQL SELECT *
FROM `DT294`
where sp285>0;
LEFT JOIN (Sales)
LOAD id as IdProduct,
descr as Product,
sp141 as AccountigPrice ;
SQL SELECT *
FROM `sc156` where isfolder=2;
Here it the test script
/*******This table is just for data*******/
salesData:
Load * INLINE
[sp283,sp285, sp284, sp286
PR1 ,1,4,20
PR2 ,3,5,25
];
Sales:
LOAD
sp283 as IdProduct,
sp285 as Pricefor1,
sp284 as Amount,
sp285*sp284 as PricelistPrice ,
sp286 as SalesPrice
RESIDENT salesData;
/********this table is for data only *******/
ProductsData:
Load * INLINE
[id , descr, sp141
PR1,Product 1, 15
PR2, product 2, 10
];
Product:
LOAD id as IdProduct,
descr as Product,
sp141 as AccountigPrice
RESIDENT ProductsData;
Left Join(Sales)
LOAD
IdProduct,
AccountigPrice
RESIDENT Product;
SalesAggr:
Load
IdProduct,
sum((SalesPrice-AccountigPrice)*Amount) as Margin
RESIDENT Sales
GROUP BY IdProduct;
Here it the test script
/*******This table is just for data*******/
salesData:
Load * INLINE
[sp283,sp285, sp284, sp286
PR1 ,1,4,20
PR2 ,3,5,25
];
Sales:
LOAD
sp283 as IdProduct,
sp285 as Pricefor1,
sp284 as Amount,
sp285*sp284 as PricelistPrice ,
sp286 as SalesPrice
RESIDENT salesData;
/********this table is for data only *******/
ProductsData:
Load * INLINE
[id , descr, sp141
PR1,Product 1, 15
PR2, product 2, 10
];
Product:
LOAD id as IdProduct,
descr as Product,
sp141 as AccountigPrice
RESIDENT ProductsData;
Left Join(Sales)
LOAD
IdProduct,
AccountigPrice
RESIDENT Product;
SalesAggr:
Load
IdProduct,
sum((SalesPrice-AccountigPrice)*Amount) as Margin
RESIDENT Sales
GROUP BY IdProduct;
Devang,
Try to use "[ code ]" and "[ / code ]" without spaces tags for code. And use Preview before submit a post
Thank you,
I did and the preview was correct, Some how it got misaaligned in the post, I corrected it now.