topic Re: Trim Characters in QlikView

Trim Characters

evansabres — Thu, 09 Apr 2015 17:40:56 GMT

Hello -

I have a field called Order

The records in that field vary in length but end in _FY15. (The field also starts with XXX_)

How can I trim the _FY15 keeping in mind that each ad name has a different number of characters?

I already have removed the XXX_ by doing the following in my script:

Order,
RIGHT([Order], Len([Order]) -4) AS orderSHRTNME,

This has allowed me to remove the XXX_, but now I want to remove the _FY15.

Thank you.

Re: Trim Characters

sunny_talwar — Thu, 09 Apr 2015 17:50:43 GMT

There are couple of ways

1) Replace(Order, '_FY15', '')

2) Mid(Order, 5, Len(yourFieldName) - 4) //Assuming you still have XXX_ in your field I am starting from 5

HTH

Best,

Sunny

Re: Trim Characters

evansabres — Thu, 09 Apr 2015 17:55:51 GMT

Is this one line in the script or two? Not working for me

Re: Trim Characters

sunny_talwar — Thu, 09 Apr 2015 17:59:08 GMT

In the script like this:

Mid(Order, 5, Len(Order) - 4) as orderSHRTNME

Replace(Replace(Order, 'XXX_', ''), '_FY15', '') as orderSHRTNME

Re: Trim Characters

sunny_talwar — Thu, 09 Apr 2015 18:02:35 GMT

My bad:

Try this:

Table:

LOAD Order,

Mid(Order, 5, (Len(Order) - 9)) as OrderShortName;

LOAD * INLINE [

Order

XXX_123_FY15

XXX_345_FY15

];

Best,

Sunny

Re: Trim Characters

evansabres — Thu, 09 Apr 2015 18:04:54 GMT

This worked beautifully. Thank you.

Re: Trim Characters

sunny_talwar — Thu, 09 Apr 2015 18:10:58 GMT

No problem, sorry for the confusion earlier. Did not do a test, just posted it from my memory. the second input for Mid is not the end point, but number of characters from the starting point to include.

HTH

Best,

Sunny