topic Re: How to trim a field value at a load from database? in QlikView

How to trim a field value at a load from database?

Thu, 26 Jun 2014 11:07:29 GMT

Hi,

I am loading a field from a database, the record values needs to be trimmed on both beginning and end

example:

00000063359701549001538000000000

I need the first six 0's and last 8 0's to be removed, min you the 9th 0 from the end is needed, I don't want that to be removed.

Any ideas?

Michiel_QV_Fan — Thu, 26 Jun 2014 11:23:23 GMT

load step4&'0' as end_result;

load replace(step3, ' ', 0) as step4;

load rtrim(step2) as step3;

load LTrim(step1) as step2;

load

replace(00000063359701549001538000000000, '0',' ') as step1

I separated the statements,Maybe you want to combine them in 1 string but this will do the job.

Anonymous — Thu, 26 Jun 2014 11:46:29 GMT

Hi,

if the number of digits in the record value are same,then its better to count the numbers and use left and right fucntions 'cause that's easy. Ex:

=left(right(00000063359701549001538000000000,26),18)
if not, then as Michiel explained we have to go round the bush..

HTH

ravi N.

Michiel_QV_Fan — Thu, 26 Jun 2014 11:51:52 GMT

Yes

Anonymous — Thu, 26 Jun 2014 12:06:30 GMT

(Y)

yduval75 — Thu, 26 Jun 2014 12:42:53 GMT

Replace(LTrim(Replace([YourField], '0', ' ')), ' ', '0')

Thu, 26 Jun 2014 13:15:34 GMT

Thanks guys!

I ended up saying:

mid(FIELDNAME,7,18) as Fieldname

Thank you