Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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
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
];
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
Hi
This is what the record in the script editor looks like. I will try out the instructions above as well. Thanks
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,
];
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' ;
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
];
Thank you swuehl. It works