I need to update my current code to convert a reading value (from table 'VisionReadings') from one unit to another. The intention here is to process this one source unit at a time.
I need something for each ReadingsId;
if SourceUnit = 'Unit' and DestinationUnit ='Unit' and 'date' between ValidFrom and ValidTo then ConversionFactor * ReadingValue
(e.g.
ReadingsId = '206742720'
SourceUnit = 'kWh'
DestinationUnit = 'tCO2'
date = '01/05/2017'
then (readingValue) 344.3 * (ConversionFactor) 0.00018381 = 0.063286
My current code (below) is based on @MarcoWedel 's answer to the following query
Solved: inner join tables between date range - Qlik Community - 656740
@vinieme12
[VisionConversions]:
LOAD
SourceUnit,
DestinationUnit,
StatusCode,
ValidFrom,
ValidTo,
ConversionFactor
FROM [lib://Admin - Data Loaders:DataFiles/d365_conversionfactors.qvd]
(qvd);
[VisionConvertedUnits]:
IntervalMatch(DateId)
load
ValidFrom
,ValidTo
resident [VisionConversions];
left join ([VisionConversions])
load * Resident VisionReadings;
drop table [VisionConversions];
VisionConversions:
| SourceUnit |
DestinationUnit |
StatusCode |
ValidFrom |
ValidTo |
ConversionFactor |
|
kWh
|
tCO2
|
Active
|
01/04/2014
|
31/03/2015
|
0.00018456
|
|
kWh
|
tCO2
|
Active
|
01/04/2015
|
31/03/2016
|
0.00018578
|
|
kWh
|
tCO2
|
Active
|
01/04/2016
|
31/03/2017
|
0.00018364
|
|
kWh
|
tCO2
|
Active
|
01/04/2017
|
31/03/2018
|
0.00018381
|
|
kWh
|
tCO2
|
Active
|
01/04/2018
|
31/03/2019
|
0.00018362
|
|
tCO2
|
Buy to Comply £
|
Active
|
01/01/2013
|
31/12/2016
|
12
|
|
tCO2
|
Buy to Comply £
|
Active
|
01/01/2017
|
31/12/2020
|
14
|
|
tCO2
|
Buy to Comply £
|
Active
|
01/01/2021
|
31/12/2024
|
18
|
