topic Re: I want to split a string into 35 charcters. in QlikView

I want to split a string into 35 charcters.

Thu, 02 Jul 2015 17:42:54 GMT

Hi,

It;s urgent.I tried a lot but i didnt get it:

a string has many charcters :

1st 35 charcters into one field but should complete a word .

ex: the premium life insurance corporate company which has a filed name NM

it comes:

left(NM,35)

result:

the premium life insurance corporat into NM1

e company into NM2

but i want

the premium life insurance into NM1

corporate company into NM2

pls help.Thanks in advance

Re: I want to split a string into 35 charcters.

Qrishna — Thu, 02 Jul 2015 17:57:34 GMT

Hi uma,

use mid function instead of left. i.e mid(NM,27).

see attached for the script.

Thanks

Krishna

Re: I want to split a string into 35 charcters.

sunny_talwar — Thu, 02 Jul 2015 18:13:06 GMT

Try this script:

Table:

LOAD *,

Left(Field, (Index(Left(Field, 35), ' ', -1))) as NM1,

Right(Field, (Len(Field) - (Index(Left(Field, 35), ' ', -1)))) as NM2 ;

LOAD * Inline [

Field

the premium life insurance corporate company

];

Output

Re: I want to split a string into 35 charcters.

Thu, 02 Jul 2015 18:21:45 GMT

sunindia ur script not working excatly how i want

krishna can u write script in here i cant open .qvw file outside (restricted in office)

anybody script here pls

Re: I want to split a string into 35 charcters.

Qrishna — Thu, 02 Jul 2015 18:24:12 GMT

tab_tmp:

load * inline [

the premium life insurance corporate company

];

tab:

LOAD

NM ,

mid(NM,27) as [NM 1]

resident tab_tmp;

drop table tab_tmp;

Re: I want to split a string into 35 charcters.

sunny_talwar — Thu, 02 Jul 2015 18:27:29 GMT

Try this:

Table:

LOAD *,

Left(Field, 35) as OldNM,

If(Len(Field) <= 35, Field, Left(Field, (Index(Left(Field, 36), ' ', -1)))) as NM1,

If(Len(Field) <= 35, '', Right(Field, (Len(Field) - (Index(Left(Field, 35), ' ', -1))))) as NM2 ;

LOAD * Inline [

Field

the premium life insurance corporate company

];

Re: I want to split a string into 35 charcters.

Thu, 02 Jul 2015 18:28:33 GMT

this NM is a field name which has 7,000 columns with different number of chacters.not specific to this.

the premium life insurance corporate company is just example.

Re: I want to split a string into 35 charcters.

Qrishna — Thu, 02 Jul 2015 18:31:25 GMT

post the qvw with just some sample data and the common requirement on that column.

Re: I want to split a string into 35 charcters.

Thu, 02 Jul 2015 18:33:19 GMT

is there any way we can write it in expression in straight table

as i said i have a table with 7,000 records in that i am pulling NM field and dividing it into NM1,NM2

by using (set analysis) expression.

PLs help

Re: I want to split a string into 35 charcters.

sunny_talwar — Thu, 02 Jul 2015 18:35:51 GMT

Did you try the new script?

Table:

LOAD *,

Left(Field, 35) as OldNM,

If(Len(Field) <= 35, Field, Left(Field, (Index(Left(Field, 36), ' ', -1)))) as NM1,

If(Len(Field) <= 35, '', Right(Field, (Len(Field) - (Index(Left(Field, 35), ' ', -1))))) as NM2 ;

LOAD * Inline [

Field

the premium life insurance corporate company

];

If it works on the back end, you should be able to use the same in the straight table as expressions also.

HTH

Best,

Sunny

Re: I want to split a string into 35 charcters.

Qrishna — Thu, 02 Jul 2015 18:37:19 GMT

Ofcourse you can.

But as i said earlier i need to see some sample data.

paste some 10-15 records here.

Re: I want to split a string into 35 charcters.

Thu, 02 Jul 2015 18:44:49 GMT

sunindia Yeah it is working for almost ....still I am checking if any exceptions

Thank you

very much

Re: I want to split a string into 35 charcters.

sunny_talwar — Thu, 02 Jul 2015 18:47:30 GMT

Cool, once it works in the back end try these expressions:

Expression 1:

If(Len(Field) <= 35, Field, Left(Field, (Index(Left(Field, 36), ' ', -1))))

Expression 2:

If(Len(Field) <= 35, '', Right(Field, (Len(Field) - (Index(Left(Field, 35), ' ', -1)))))

Re: I want to split a string into 35 charcters.

Anonymous — Thu, 02 Jul 2015 19:10:47 GMT

Another version:

NM1 = left(vText, 35 + index(mid(vText, 36),' '))

NM2 = mid(vText, 35 + index(mid(vText,36),' '))

(Where vText is a variable where I kept the text for testing - replace with your field name)

Re: I want to split a string into 35 charcters.

sunny_talwar — Thu, 02 Jul 2015 20:24:28 GMT

I think Michael‌ the problem with 35 + is that it will include the half word in NM1, whereas he wanted half word to go in NM2. I was trying this out first, but when I saw that he want the NM1 to be less than 35 completed words I had to take the longer route to get the solution. But I am sure there might be an easier way to get what he is looking for. Let me know what you think?

Best,

Sunny

Re: I want to split a string into 35 charcters.

Anonymous — Thu, 02 Jul 2015 20:50:15 GMT

Maybe this:

=left(vText, index(left(vText,35), ' ',-1))

=mid(vText, index(left(vText,35), ' ',-1)+1)

The

index(left(vText,35), ' ',-1)

returns position of the last space in the first 35 characters. After that we just need left() and mid() to cut out the pieces...

anyway sunindia, not much different from your solution

Re: I want to split a string into 35 charcters.

sunny_talwar — Thu, 02 Jul 2015 20:53:07 GMT

The problem then is that if I have a phrase like: I like sunny (which is 12 character long) it shows I like as NM1 and sunny as NM2 because then index(left(vText,35), ' ',-1) finds space before sunny to divide it.

Re: I want to split a string into 35 charcters.

Anonymous — Thu, 02 Jul 2015 20:57:41 GMT

This one is a small problem - and you have the if() for that already.

Re: I want to split a string into 35 charcters.

Thu, 02 Jul 2015 21:02:48 GMT

Guys , right now my server is down . I am waiting

Will update once I tried with others too . It's very

Confidential data I can't post any . I Apolozise .

Thanks & Regards

Re: I want to split a string into 35 charcters.

sunny_talwar — Thu, 02 Jul 2015 21:40:08 GMT

Hahahaha, that makes perfect sense Michael‌

Best,

Sunny