Fetching only particular characters in field value

[#1]

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.

A:

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

[antoniotiman · Accepted solution]

Hi Logesh,

maybe this

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

Regards,

Antonio

[swuehl]

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,

...

[its_anandrjs]

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

Please check now

A:

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

];

[Kushal_Chawda]

You can also use

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

[#6]

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

[#7]

Edit: the Mid solution is neater..

[#8]

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

Regards,

Logesh

[Kushal_Chawda]

You can also mark some of the solution as helpful