Qlik Community

Qlik Sense App Development

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

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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. 

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. 

 

sampleqlik.PNG

 

Thanks!