topic Re: Remove 1st character and then read only next 7 characters in QlikView

Remove 1st character and then read only next 7 characters

kailashkotak91 — Thu, 22 Feb 2018 23:31:36 GMT

Hello,

I have a field where the data is very inconsistent. I want to remove the 1st character and ready the next 7 characters from there and call the blanks N/A. For example

Resource ID

[1234567 14]haslkdjasl

I want to pull only 1234567 from the resource ID field and parse out [ ] and remaining text.

Regards,

Re: Remove 1st character and then read only next 7 characters

Digvijay_Singh — Fri, 23 Feb 2018 03:29:58 GMT

May be this -

Mid(ResourceID,2,8) as ResourceID

Re: Remove 1st character and then read only next 7 characters

kailashkotak91 — Fri, 23 Feb 2018 19:43:49 GMT

Thanks for your reply! Appreciate it.

Just wanted to confirm, the data quality is really bad, i have few entries that directly does not have '[' in the beginning.

Can it be something like if(resource ID starts with [ then Mid(ResourceID,2,8) as ResourceID else if(resource ID starts with a number then some condition...

Can you help with this ?

Thanks in advance

Regards,

Kailash Kotak

Re: Remove 1st character and then read only next 7 characters

Digvijay_Singh — Sat, 24 Feb 2018 18:18:58 GMT

Something like this may be -

if(left(ResourceID,1)='[',Mid(ResourceID,2,8),

if(Match(Left(ResourceID,1),0,1,2,3,4,5,6,7,8,9),<Write here other expression like Mid>))

Re: Remove 1st character and then read only next 7 characters

Digvijay_Singh — Sat, 24 Feb 2018 18:21:32 GMT

If you have situations like sometimes first character is number and sometimes '[' then this might also work -

Replace(Mid(ResourceID,1,8),'[','') as ResourceID