IntervalMatch on conversion factor table

BenjaminCoe1440 — Wed, 30 Nov 2022 16:03:18 GMT

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

topic IntervalMatch on conversion factor table in QlikView

IntervalMatch on conversion factor table