Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Read about the latest Qlik Community enhancements on the Community News blog!
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Unit of Measure conversion

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. 

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
applymap('UOM_Map', %UOMKey, 'No units') as UOMUnits,
applymap('PerformanceItemMap', %PerformanceItemSK) as PerformanceItemMaster;
LOBSK&'|'&PerformanceItemSK as %UOMKey,
FactSK, // primary
PersonSK as %PersonSK, // key
PerformanceItemSK as %PerformanceItemSK,
date(AssessmentDate, 'MM/DD/YYYY') as AssessmentDate, // key
FROM [lib://QVD/Assessments.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. 





0 Replies