8 Replies Latest reply: Sep 13, 2017 5:33 AM by kushal chawda RSS

    Fetching only particular characters in field value

    Logesh rajan

      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