Hello, I have a data set that I'm having trouble with. My returned value for feet and inches is 10'11 as a string. Our salespeople get paid per foot, so I need to turn that into a number that I can plug into a mathematical equation. How can set up Qlik or SQL to convert?
Additonal info:
There is no guarantee that there will be inches attached to the length, much of the time it is only shown in feet, but the ' is always in place.
I thought about truncating, but we could have anything from 8 feet up to 150 feet.
Here's my current query.
select bsc.id,
writtendate,
commissionedsalesperson,
cl.name as clname,
bmtdefaultloc,
splitpercentage,
store_name,
worksheetprofit,
bsc.name as customer,
bsc_commissions.bsc_id,
length,
bmi.year,
bmi.description
from bsc
left outer join bsc_commissions
on bsc.id = bsc_commissions.bsc_id
left outer join cl
on bsc_commissions.commissionedsalesperson = cl.cl_id
left outer join store_info
on cl.bmtdefaultloc = store_info.store_id
left outer join bsc_boats
on bsc.id = bsc_boats.bsc_id
left outer join bmi
on bsc_boats.boatmodelid = bmi.id
where writtendate >= '01/01/2016'
and commissionedsalesperson is not null
and cl.name is not null
and bsc.status = 'o'
and splitpercentage >= '0.5'
order by commissionedsalesperson asc;
