Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Logesh,
maybe this
Load Left(Group_Name,Index(Group_Name,':',-1)-1) as Group_Name
Regards,
Antonio
Hi Logesh,
maybe this
Load Left(Group_Name,Index(Group_Name,':',-1)-1) as Group_Name
Regards,
Antonio
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,
...
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
];
You can also use
mid(Group_Name,1,Index(Group_Name,':',-1)-1) as Group_Name
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..
Edit: the Mid solution is neater..
Thank you all for your answers. By the way all the solutions that you had provided are working
Regards,
Logesh
You can also mark some of the solution as helpful