How to create a field from filelds of 2 different tables

[#1]

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?

[#2]

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.

[#3]

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..

[#4]

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;

[#5]

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;

 

[#6 · Accepted solution]

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;

[#7]

Devang,

Try to use "[ code ]" and "[ / code ]" without spaces tags for code. And use Preview before submit a post

[#8]

Thank you,

I did and the preview was correct, Some how it got misaaligned in the post, I corrected it now.