topic Re: Fetching only particular characters in field value in QlikView

Fetching only particular characters in field value

Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I need your help to fetch only the certain characters from the field value. Let's take i have a Group_Name field with 3 values.

Load * Inline [

Group_Name

Additional Usage: Offer,

Free Product: Offer Free and Free Application Utilities: Category,

My Account: Usage: Application Standard or Deluxe: Sell

];

First Scenario - 'Additional Usage: Offer', I have to fetch only the part of the string i.e before the colon. My result should be 'Additional Usage'. In other words i have to delete the characters after the last colon.

Second Scenario - 'Free Product: Offer Free and Free Application Utilities: Category', My result for this value should be 'Free Product: Offer Free and Free Application Utilities'. In other words i have to delete the characters after the last colon.

I had used SubField(Group_Name,':',1), but it is not fetching the proper results.

Note: Below is the logic used to achieve the expected results in SQL. Could you help me to achieve this in Qlikview

[Group_Name_New] = reverse(substring(reverse(Group_Name) , CHARINDEX ( ':' , reverse(Group_Name))+1 , len(reverse(Group_Name)) - (CHARINDEX ( ':' , reverse(Group_Name))-1))).

Thanks and Regards,

Logesh

Re: Fetching only particular characters in field value

antoniotiman — Wed, 13 Sep 2017 06:28:31 GMT

Hi Logesh,

maybe this

Load Left(Group_Name,Index(Group_Name,':',-1)-1) as Group_Name

Regards,

Antonio

Re: Fetching only particular characters in field value

swuehl — Wed, 13 Sep 2017 06:28:38 GMT

Maybe like

=Left( 'Free Product: Offer Free and Free Application Utilities: Category',Index( 'Free Product: Offer Free and Free Application Utilities: Category',':',-1)-1)

in the script:

LOAD

Left(Group_Name,Index(Group_Name,':',-1)-1) as GroupNameNew,

...

Re: Fetching only particular characters in field value

its_anandrjs — Wed, 13 Sep 2017 06:29:21 GMT

Try with this check this will it worked for you with Subfield and combination of the Len function.

Please check now

Load *,if( Len(Group_Name) > 16, subfield(Group_Name,':',2), subfield(Group_Name,':',1)) as NewGroup_Name;

Load * Inline [

Group_Name

Additional Usage: Offer,

Free Product: Offer Free and Free Application Utilities: Category,

My Account: Usage: Application Standard or Deluxe: Sell

];

Re: Fetching only particular characters in field value

Kushal_Chawda — Wed, 13 Sep 2017 06:45:50 GMT

You can also use

mid(Group_Name,1,Index(Group_Name,':',-1)-1) as Group_Name

Re: Fetching only particular characters in field value

Wed, 13 Sep 2017 06:51:06 GMT

What about using substringcount, something similar to this;

left(Group_Name,index(Group_Name':',SubStringCount(Group_Name,':'))-1)

"Take all characters left of the last substring ":"."

It might not be pretty but it should give you what you need, it shouldn't be bad performance-wise either..

Re: Fetching only particular characters in field value

Wed, 13 Sep 2017 06:52:09 GMT

Edit: the Mid solution is neater..

Re: Fetching only particular characters in field value

Wed, 13 Sep 2017 09:25:21 GMT

Thank you all for your answers. By the way all the solutions that you had provided are working

Regards,

Logesh

Re: Fetching only particular characters in field value

Kushal_Chawda — Wed, 13 Sep 2017 09:33:14 GMT

You can also mark some of the solution as helpful