topic How to multiply from one cell to another? in QlikView

How to multiply from one cell to another?

Mon, 12 Aug 2013 09:50:30 GMT

How do i multiply the following tables in code for the script editor? For example I need to be able to multiple 55.02 by 2795 to give me "153,70.90" the "Grand Total". What script do I write?

Total Area per Key, 55.02 , 99.33 , 118.31 , 123.11 , 127.91

Overall Total Cost per m2, 2795 , 2954 , 3397 , 3677 , 3898

Grand Total,

Thanks

Re: How to multiply from one cell to another?

Miguel_Angel_Baeyens — Mon, 12 Aug 2013 09:56:53 GMT

Hi,

Are these fields in the same record? If yes, the answer is like:

Table1:
LOAD *,
     [Total Area per Key] * [Overall Total Cost per m2] AS [Grand Total]; // this is a new field you create into QlikView
SQL SELECT *
FROM SourceTable;

If not, then there must be a key field that links one table to the other, and the use of ApplyMap() will do here:

TableAreaMap:
MAPPING LOAD KeyField,
     [Total Area per Key];
SQL SELECT KeyField, [Total Area per Key] 
FROM SourceTable;
TableTotal:
LOAD *,
     ApplyMap('TableAreaMap', KeyField) AS [Total Area per Key], // so you have this value in the second table
     ApplyMap('TableAreaMap', KeyField) * [Overall Total Cost per m2] AS [Grand Total]; // this is a new field you create into QlikView
SQL SELECT *
FROM SourceTable2;

Hope that helps.

Miguel

Re: How to multiply from one cell to another?

Mon, 12 Aug 2013 10:28:36 GMT

This is what the record in the script editor looks like. I will try out the instructions above as well. Thanks

OverallCostperBuildNewKey:

Re: How to multiply from one cell to another?

Mon, 12 Aug 2013 10:59:48 GMT

Is that correct? Its not working

OverallCostperBuildNewKey:

LOAD * INLINE [

IHG Brand (New Build),Holiday Inn Express (New Build), Holiday Inn (New Build), Crowne Plaza (New Build), Indigo Hotel (New Build), InterContinental (New Build)

Total Area per Key, 55.02 , 99.33 , 118.31 , 123.11 , 127.91

Overall Total Cost per m2, 2795 , 2954 , 3397 , 3677 , 3898

Grand Total,

];

Table1:

LOAD *,

[Total Area per Key] * [Overall Total Cost per m2] AS [Grand Total];

SQL SELECT *

FROM SourceTable 'OverallCostperBuildNewKey' ;

Re: How to multiply from one cell to another?

swuehl — Mon, 12 Aug 2013 11:07:20 GMT

Try like this:

OverallCostperBuildNewKey:

LOAD *,

[Total Area per Key] * [Overall Total Cost per m2] AS [Grand Total]

INLINE [
IHG Brand (New Build), Total Area per Key, Overall Total Cost per m2

Holiday Inn Express (New Build), 55.02, 2795

Holiday Inn (New Build),99.33, 2954

Crowne Plaza (New Build), 118.31, 3397

Indigo Hotel (New Build), 123.11, 3677

InterContinental (New Build),127.91, 3898
];

Re: How to multiply from one cell to another?

Mon, 12 Aug 2013 11:20:26 GMT

Thank you swuehl. It works