10 Replies Latest reply: Apr 18, 2018 8:56 AM by Sasidhar Parupudi RSS

    sub String

    Prithvi K

      Please help with getting substring from multiple values

       

      eg:

      Department xxxxx   asbc     closed

      Department  yyyyyyy             opened

      Department  zzzzz qwer tyug     Reopen

       

      I need to get the string between  'Department'  and 'Closed', 'Department and 'opened', 'Department' and 'Reopen'  into single field

       

      Result:

       

      New field

      xxxxx   asbc

      yyyyyyy

      zzzzz qwer tyug

        • Re: sub String
          Tomasz Truszkowski

          Use mid function to cut first 10 and last 6 characters.

          Tomasz

          • Re: sub String
            Sasidhar Parupudi

            Trim(TextBetween(Your Field,'Department ','closed'))

              • Re: sub String
                Sasidhar Parupudi

                Apologies, didnt read the question properly.. May be try

                 

                Pick(

                Match([Your Field],'closed,'opened','Reopen'),

                Trim(TextBetween([Your Field],'Department ','closed')),

                Trim(TextBetween([Your Field],'Department ','opened')),

                Trim(TextBetween([Your Field],'Department ','Reopen'))

                )



                XZZ:


                LOAD CV,

                 

                Pick(


                WildMatch(CV,'*closed','*opened','*Reopen'),


                Trim(TextBetween(CV,'Department','closed')),


                Trim(TextBetween(CV,'Department','opened')),


                Trim(TextBetween(CV,'Department','Reopen'))


                ) As CV1


                INLINE


                [CV

                Department xxxxx   asbc     closed

                Department  yyyyyyy             opened

                Department  zzzzz qwer tyug     Reopen

                ];

              • Re: sub String
                chaitanya yelisetty

                Try Something like this


                FINAL:

                Load CV,

                Pick(WildMatch(CV,'*closed*','*opened*','*Reopen*'),

                mid(CV,11,index(CV,'closed')-11),

                mid(CV,11,index(CV,'opened')-11),

                mid(CV,11,index(CV,'Reopen')-11)

                )

                as XXX

                ;

                XZZ:

                LOAD * INLINE

                [CV

                Department xxxxx   asbc     closed


                Department  yyyyyyy             opened


                Department  zzzzz qwer tyug     Reopen


                ];


                Hope this helps


                Thanks

                CY

                  • Re: sub String
                    Sasidhar Parupudi

                    Try this

                    XZZ:


                    LOAD CV,

                    WildMatch(CV,'*closed*','*opened*','*Reopen','*Opens*') as xxx,

                    Pick(


                    WildMatch(CV,'*closed*','*opened*','*Reopen*','*Opens*'),


                    Trim(TextBetween(CV,'Department','closed')),

                    Trim(TextBetween(CV,'Department','opened')),

                    Trim(TextBetween(CV,'Department','Reopen')),

                    Trim(TextBetween(CV,'Department','Opens'))


                    ) As CV1


                    INLINE


                    [CV

                    Department xxxxx   asbc     closed

                    Department  yyyyyyy             opened

                    Department  zzzzz qwer tyug     Reopen

                    Department  AAAAAAAAAAAAAAAAA   closed today

                    Department  asd  cjffd ert qh  Opens every monday to friday

                    ];

                  • Re: sub String
                    Tamil arasu

                    Hi Prithvi,

                     

                    Script:


                    Data:
                    Load *, Trim(TextBetween(CV,'Department',SubField(CV,' ',-1))) as NewField;
                    LOAD * INLINE [
                        CV
                        Department xxxxx asbc closed
                        Department yyyyyyy  opened
                        Department zzzzz qwer tyug Reopen
                    ];
                    
                    
                    

                     

                    If your department is dynamic data then try

                     

                    Data:
                    Load *, Trim(TextBetween(CV,SubField(CV,' ',1),SubField(CV,' ',-1))) as NewField;
                    LOAD * INLINE [
                        CV
                        Department xxxxx asbc closed
                        Department yyyyyyy  opened
                        Department zzzzz qwer tyug Reopen
                    ];
                    
                    
                    

                     

                    Result:

                    Capture.PNG

                    Note: This solution is purely based on your above data. If you have more scenarios then you have to post more examples.

                    • Re: sub String
                      Sasidhar Parupudi

                      have you managed to get this working?