Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
kailashkotak91
New Contributor III

Remove 1st character and then read only next 7 characters

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,

1 Solution

Accepted Solutions
Highlighted
Digvijay_Singh
Honored Contributor III

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

May be this -

Mid(ResourceID,2,8) as ResourceID

View solution in original post

4 Replies
Highlighted
Digvijay_Singh
Honored Contributor III

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

May be this -

Mid(ResourceID,2,8) as ResourceID

View solution in original post

Highlighted
kailashkotak91
New Contributor III

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

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

Highlighted
Digvijay_Singh
Honored Contributor III

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

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>))

Highlighted
Digvijay_Singh
Honored Contributor III

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

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

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