5 Replies Latest reply: May 3, 2016 5:41 PM by Marco Wedel RSS

    Convert Feet and Inches to Inches

    Jerry Kobriger

      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;