How to split string several time based on substring count.

[vvira1316]

I have a string column that has several approval statuses. I have used substringcount function to find out count of each substring that I want to locate. Based on count and length function now I want to split the string into multiple string for further processing.

I'm including the original string and providing substrings that I want to use.

*** Begin of Original String ****

26-Jun-2013 10:00:24 – Ka Be  (Approval history) Group approval for ChangeQAReviewer approved by all users (QA Post Approval).

20-Jun-2013 11:46:45 – Ar Ra (Approval history) Group approval for SUM-SAN-PCHA approved by user Ar Ra (Business Owner Post Approval).

12-Jun-2013 15:44:16 - Ro Mi (Approval history) Group approval for ChangeQAReviewer approved by user Ro Mi (QA Post Approval).

12-Jun-2013 15:41:56 - Ar Ra (Approval history) Group approval for SUM-SAN-PCHA approved by user Ar Ra (Business Owner Post Approval).

12-Jun-2013 15:15:06 - Ro Mi (Approval history) Group approval for ChangeQAReviewer approved by user Ro Mi (QA Pre-Approval).

12-Jun-2013 15:13:37 - Ar Ra (Approval history) Group approval for SUM-SAN-PCHA approved by user Ar Ra(Business Owner Pre-Approval).

12-Jun-2013 15:12:34 - Ar Ra (Approval history) Group approval for SUM-SAN-ITRA approved by user Ar Ra (IT Review Approval).

22-Mar-2013 12:44:37 - Ro Mi (Approval history) Group approval for SUM-SAN-QCHA approved by user Ro Mi (QA Pre-Approval).

22-Mar-2013 12:09:18 - Arn Ra (Approval history) Group approval for SUM-SAN-PCHA approved by user Ar Ra (Business Owner Pre-Approval).

22-Mar-2013 11:42:39 - Ar Ra (Approval history) Group approval for SUM-SAN-ITRA approved by user Ar Ra (IT Review Approval).

*** End of Original String ****

Different substrings based on which I want to split the strings are as follows

  (QA Post Approval).

  (Business Owner Post Approval).

  (QA Pre-Approval).

  (Business Owner Pre-Approval).

  (IT Review Approval).

for example

one substring as

22-Mar-2013 11:42:39 - Ar Ra (Approval history) Group approval for SUM-SAN-ITRA approved by user Ar Ra (IT Review Approval).

and

second substring as

12-Jun-2013 15:13:37 - Ar Ra (Approval history) Group approval for SUM-SAN-PCHA approved by user Ar Ra(Business Owner Pre-Approval).

so original string in the example has to be split into 10 different strings.

I'm aware of index and mid functions that I can leverage here but I'm struggling with load and loop. Any suggestions will be very helpful.

Thanks,

Vijay

[martin59]

HI,

i'm not in front of my computer to test the exact function, but I think you should try with Index(), mid(), and Subfield() functions.

hope that helps you,

Martin

[rwunderlich]

Do you have line breaks (chr(10)) between each of those lines in the original string?

-Rob

[vvira1316]

Hi Rob,

How do I check for it? I think there is line break.

Vijay

[Sokkorn]

Hi Vijay,

I have three case in my mind. Let start:

1. Case1: If the line break is "Enter Line" you may try SubField([StringField],Chr(10))

2. Case2: One string line with dot for each end string line, you may try SubField([StringField],'.')

3. Case3: One string line no dot. See example below:

[Data]:
LOAD * Inline [
ID, STR
1, '12-Jun-2013 15:12:34 - Arnaldo Ramos (Approval history) Group approval for SUM-SAN-ITRA approved by user Ar Ra (IT Review Approval) 22-Mar-2013 12:44:37 - Ro Mi (Approval history) Group approval for SUM-SAN-QCHA approved by user Ro Mi (QA Pre-Approval)'
];


[Data2]:
LOAD *,Trim(tmpSTR) & 'Approval)' AS NewSTR Where Len(tmpSTR)>0;
LOAD
  ID,
  SubField(STR,'Approval)') AS tmpSTR
Resident [Data];


DROP Field tmpSTR;
DROP Table [Data]

Regards,

Sokkorn

[vvira1316]

This string is part of a row in data table. This functionality has to be done for all rows. Can you please edit your post and remove the name in the string before (Approval history) . Thanks in advance.

[vvira1316]

Hi,

I'm able to get positions of substring using Mid, Index, and For loop functions. I'm running into couple of issues once the process is complete.

The first Issue is as follows.

I'm suppose to get 10 count information for positions. I'm getting 11 for some reason with 1 one repeating. I've attached the screenshot of output.

The second issue is as follows.

I had modified my script to use different logic then one in the issue one. Here I'm looking for pattern ').' in the string. I got the count and with some logic I'm finding the starting position from where I want to do substring using mid function and the length of substring I will like to have.

In debug I'm getting following syntax but it is resulting in NULL value for variable.

LET StringDesc = Mid(trim($(StringDesc)), $(BegPos), $(StringLen));

LET StringDesc = Mid(trim("26-Jun-2013 10:00:24 - Ksjhj Bahjdh (Approval history) Group approval for ChangeQAReviewer approved by all users (QA Post Approval).

20-Jun-2013 11:46:45 - Arkasoa Rnsls (Approval history) Group approval for SUM-SAN-PCHA approved by user Arkasoa Rnsls (Business Owner Post Approval).

12-Jun-2013 15:44:16 - Redred Greenred (Approval history) Group approval for ChangeQAReviewer approved by user Redred Greenred (QA Post Approval).

12-Jun-2013 15:41:56 - Arkasoa Rnsls (Approval history) Group approval for SUM-SAN-PCHA approved by user Arkasoa Rnsls (Business Owner Post Approval).

12-Jun-2013 15:15:06 - Redred Greenred (Approval history) Group approval for ChangeQAReviewer approved by user Redred Greenred (QA Pre-Approval).

12-Jun-2013 15:13:37 - Arkasoa Rnsls (Approval history) Group approval for SUM-SAN-PCHA approved by user Arkasoa Rnsls (Business Owner Pre-Approval).

12-Jun-2013 15:12:34 - Arkasoa Rnsls (Approval history) Group approval for SUM-SAN-ITRA approved by user Arkasoa Rnsls (IT Review Approval).

22-Mar-2013 12:44:37 - Redred Greenred (Approval history) Group approval for SUM-SAN-QCHA approved by user Redred Greenred (QA Pre-Approval).

22-Mar-2013 12:09:18 - Arkasoa Rnsls (Approval history) Group approval for SUM-SAN-PCHA approved by user Arkasoa Rnsls (Business Owner Pre-Approval).

22-Mar-2013 11:42:39 - Arkasoa Rnsls (Approval history) Group approval for SUM-SAN-ITRA approved by user Arkasoa Rnsls (IT Review Approval)."), 1, 132)

I'll appreciate some guidance.

Thanks,

Vijay

[vvira1316]

I've figured out how to split the string. I had to use CHR(39) to remove the second error. Now I'm struggling with conditional insertion of records and appending the tablewhile processing the For Loop.