Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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?

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;

5 Replies
MVP
MVP

Re: Convert Feet and Inches to Inches

Try something like

LOAD

     YourField,

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

     ...

;

SELECT ...

edit: Used Rangesum()

MVP
MVP

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,

Not applicable

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

MVP
MVP

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:

Preceding Load

Re: Convert Feet and Inches to Inches

Hi,

another solution might be:

QlikCommunity_Thread_215416_Pic1.JPG

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