Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Return single string

Hi all,

I have a Customer field, which contains a proper name, for example 'Sony', which has a duplicate, yet different name in the same field, example

'Sony      123a'. I'd like to strip out the 123a and just return the 'Sony', or at least the first record.

Is there any function that could remove or delete the remaining characters after 'Sony'

Thank you

2 Replies
nirav_bhimani
Contributor III

Re: Return single string

Hi,

Try this syntax it will give you output as you want.

=subfield(PurgeChar('Sony      123a',chr(39)),' ')

Regards,

Nirav Bhimani

Highlighted

Re: Return single string

There are lots of ways that probably all won't work.

left(Customer,4) will return 'Sony'. But this always picks the first 4 characters

If(left(Customer,4)='Sony','Sony',Customer) will only change Customers that start with Sony to 'Sony'. But it will leave other (duplicate) Customers alone.

subfield(Customer,' ',1) as Customer will return the string before the first space. If you have Customers with a space in their regular name this won't work.

left(Customer,index(Customer,' ',-1)-1) wil return everything before the last space.

If all that doesn't work you can try using wildcard mapping. But for that you have to identify the duplicates anyway.

Maybe you need to buy a data cleansing tool. Such tools are created to do things like this.


talk is cheap, supply exceeds demand
Community Browser