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

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

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?

6 Replies
michielvandegoo
Not applicable

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

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.

ravic906
Not applicable

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

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.

michielvandegoo
Not applicable

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

Yes

ravic906
Not applicable

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

(Y)

yduval75
Not applicable

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

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

Not applicable

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

Thanks guys!

I ended up saying:

mid(FIELDNAME,7,18) as Fieldname

Thank you