Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

How to create a field from filelds of 2 different tables

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?

1 Solution

Accepted Solutions
Not applicable

How to create a field from filelds of 2 different tables

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;


View solution in original post

7 Replies
Not applicable

How to create a field from filelds of 2 different tables

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.

Not applicable

How to create a field from filelds of 2 different tables

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

Not applicable

How to create a field from filelds of 2 different tables

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;


Not applicable

How to create a field from filelds of 2 different tables

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;




Not applicable

How to create a field from filelds of 2 different tables

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;


View solution in original post

Not applicable

How to create a field from filelds of 2 different tables

Devang,

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

Not applicable

How to create a field from filelds of 2 different tables

Thank you,

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