topic Re: Search a string to pull out data. in QlikView

Search a string to pull out data.

Mon, 25 Mar 2013 13:15:48 GMT

I am importing a string from an excel file and i need to clean up one of the colums and make it two columns.

The first thing i need to do was get rid of the first two charcaters whihc was no big deal.

The next part is where i am having the issue i am trying to pull out a chunk of the string to create another field.

My issue is the chunk of data for 90% of the records is the last 10 bytes of the string so thats pretty simply but for the other 10% of the records the chunk is in the first 9 bytes of the string.

ZZXXXXXXXXX XXXXXXXXXXX XXXXX XXXXXXXXX XXXXXX XXXXX 9999999999

ZZ999999999XXXXXXXXX XXXXXXXXXXX XXXXX XXXXXXXXX XXXXXX XXXXX

So to get rid of the first two bytes i just did this which worked fine to get rid of the two bytes i dont want.

right(trim(@2),len(trim(@2))-2) as String1

but to pull out the data for the new field I was simply doing a right function but i need to to pull either the right most 10 characters or the left most 9 characters depending on where the number happens to be and how long it is

right(trim(@2),10) as String2

Re: Search a string to pull out data.

kaushiknsolanki — Mon, 25 Mar 2013 13:21:51 GMT

Hi,

If the required data is only number then you can try this.

Keepchar(Field,'1234567890') as New_Field.

This will remove every other character then numbers.

Hope this will help.

Regards,

Kaushik Solanki

Re: Search a string to pull out data.

Mon, 25 Mar 2013 13:31:19 GMT

I wish I was that lucky the field has product descriptions in it along with which has things like size and pack size.

Re: Search a string to pull out data.

Mon, 25 Mar 2013 14:37:49 GMT

This is kind of what i came up with to do what i was looking for.

IF (isnum(left(PURGECHAR(PurgeChar(@2,'"'),'ÿ+'),11)),left(PURGECHAR(PurgeChar(@2,'"'),'ÿ+'),11),RIGHT(PURGECHAR(PurgeChar(@2,'"'),'ÿ+'),10) )