Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
[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 |