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

# Convert Feet and Inches to Inches

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?

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;

• ###### Re: Convert Feet and Inches to Inches

Try something like

YourField,

Rangesum(Subfield(YourField ,chr(39),1),Subfield(YourField ,chr(39),2)/12) AS FeetFrac,

...

;

SELECT ...

edit: Used Rangesum()

• ###### Re: Convert Feet and Inches to Inches

And if you want to get the inches:

Rangesum(Subfield(YourField ,chr(39),1)*12,Subfield(YourField ,chr(39),2)) AS Inches,

• ###### Re: Convert Feet and Inches to Inches

I'm getting an error using that:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'chr' is not a recognized built-in function name.

select bsc.id,

writtendate,

commissionedsalesperson,

cl.name as clname,

bmtdefaultloc,

splitpercentage,

store_name,

worksheetprofit,

bsc.name as customer,

bsc_commissions.bsc_id,

Rangesum(Subfield(length, chr(39),1),Subfield(length, chr(39),2)/12) AS FeetFrac,

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

• ###### Re: Convert Feet and Inches to Inches

Please check my first answer, I am not using the calculations in the SQL part, but in an preceding LOAD:

• ###### Re: Convert Feet and Inches to Inches

Hi,

another solution might be:

```tabLength:
Evaluate(Replace(length,Chr(39),'+1/12*0')) as lengthFeet,
Evaluate('12*'&Replace(length,Chr(39),'+0')) as lengthInches
Inline [
length
10'
10'1
10'2
10'3
10'4
10'5
10'6
10'7
10'8
10'9
10'10
10'11
];
```

hope this helps

regards

Marco