Unit of Measure conversion

philip_radovich — Tue, 27 Aug 2019 14:34:08 GMT

I am having some trouble with unit of measure conversions. I am using a preceding load on my fact table (Assessments) to take one field (value) and convert based on the unit of measure for a performance item(PI) from a bridge table. Please see the attached sample data set. The values in the fact table are stored at the lowest unit of measure but based on the Display Unit of Measure I would like to show the values in the correct format. Below is a stripped down version of my load script.

Load*,
if(match(UoMSK, '15')and Value<1 and match(PerformanceItemSK, '23', '24', '25', '26') , Value*100,
if(match(UOMUnits,'Ft/In', 'ft/in'), Num(Round(Value/12,0.01)),
if(match(UOMUnits, 'min/sec', 'Min:Sec'), Time(Value/(3600*24),'hh:mm:ss'),
if(match(UOMUnits, 'Hrs/Min'), Time(Value/(3600*24),'hh:mm'), Value)
))) as ValueConverted
;
Load*,
applymap('UOM_Map', %UOMKey, 'No units') as UOMUnits,
applymap('PerformanceItemMap', %PerformanceItemSK) as PerformanceItemMaster;
LOAD
LOBSK,
LOBSK&'|'&PerformanceItemSK as %UOMKey,
FactSK, // primary
PersonSK as %PersonSK, // key
PerformanceItemSK as %PerformanceItemSK,
PerformanceItemSK,
AssessmentSK,
date(AssessmentDate, 'MM/DD/YYYY') as AssessmentDate, // key
Value
FROM [lib://QVD/Assessments.qvd]
(qvd);

In the Example data set Broad jumps Value is 75 inches but I would like for the value converted to display as 6' 3". With the current conversion it would show as 6.25 with no ft/in specified. The same goes for the timed PI's, 800 M Swim is stored as 1020 seconds which converted be 00:17:00 minutes. The overall goal is to have a bar chart with a filter that displays the counts of each value based on the PI selected. Any help would be appreciated.

Thanks!

topic Unit of Measure conversion in App Development

Unit of Measure conversion