topic Re: Convert Feet and Inches to Inches in QlikView

Convert Feet and Inches to Inches

Tue, 03 May 2016 15:41:23 GMT

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;

Re: Convert Feet and Inches to Inches

swuehl — Tue, 03 May 2016 15:49:21 GMT

Try something like

LOAD

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

swuehl — Tue, 03 May 2016 15:52:07 GMT

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

Tue, 03 May 2016 20:16:47 GMT

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

swuehl — Tue, 03 May 2016 21:18:18 GMT

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

Preceding Load

Re: Convert Feet and Inches to Inches

MarcoWedel — Tue, 03 May 2016 21:41:00 GMT

Hi,

another solution might be:

tabLength:
LOAD *,
    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