Would like to extract the No value from the below comments column and will you please suggest the RegEx functionality.
Sl.No. | Comments |
1 | Messages are used by youth and adults for personal, family, business and social purposes. Governmental and non-governmental organizations use text messaging for communication between colleagues. In No: 889911, the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. |
2 | Messages are used by youth and adults for personal, family, business and social purposes. Governmental No: 2010,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. |
3 | Messages are used by youth and adults for personal, family, business and social purposes. No: 222 Governmental,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. |
4 | Messages are used by youth and adults for personal, family, business and social purposes. Governmental,and non-governmental organizations use text messaging for communication between No: 5987 colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. |
5 | Messages are used by youth and adults for personal, family, business and social purposes. Governmental,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened No: 11234 earlier with emailing. |
Solved! Go to Solution.
Yeah it works and Thanks Thableaus.
Also, I’ve different scenario as below and would like to find the blocked values from the comment column.
Sl.No. | Comments |
1 | Cours: QlikView Location / Country: North America (USA) Team: 1st Team Date Completion: 10/20/2017 Student No.: AB1089 Details:Messages are used by youth and adults for personal, family, business and social purposes. Governmental No: 2010,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. Reg #: 222 Student Name: David Area Name: Analytics Course ID: 141992 Col 1 #: 35280 Faculty Name1: John Faculty Name2: TBD URL: www.urlname.com Other Code: 83476483 |
2 | Cours: QlikView Location / Country: North America (USA) Team: 1st Team Date Completion: 11/22/2018 Student No.: Z1089 Details:Messages are used by youth and adults for personal, family, business and social purposes. Governmental No: 2010,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. Reg #: 222 Student Name: David Area Name: Data 22 Course ID: 141992 Col 1 #: DS3843834343 Faculty Name1: John Faculty Name2: TBD URL: www.urlname.com Other Code: 48563 |
3 | Cours: QlikView Location / Country: North America (USA) Team: 1st Team Date Completion: 01/25/2018 Student No.: Za13439 Details:Messages are used by youth and adults for personal, family, business and social purposes. Governmental No: 2010,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. Reg #: 222 Student Name: David Area Name: .Net Course ID: 141992 Col 1 #: 2838473 Faculty Name1: John Faculty Name2: TBD URL: www.urlname.com Other Code: 4973 |
4 | Cours: QlikView Location / Country: North America (USA) Team: 1st Team Date Completion: 11/25/2017 Student No.: Z108222 Details:Messages are used by youth and adults for personal, family, business and social purposes. Governmental No: 2010,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. Reg #: 222 Student Name: David Area Name: Sun Sol Course ID: 141992 Col 1 #: 7565234 Faculty Name1: John Faculty Name2: TBD URL: www.urlname.com Other Code: 8347343234 |
5 | Cours: QlikView Location / Country: North America (USA) Team: 1st Team Date Completion: 05/15/2018 Student No.: BB31089 Details:Messages are used by youth and adults for personal, family, business and social purposes. Governmental No: 2010,and non-governmental organizations use text messaging for communication between colleagues. In the sending of short informal messages has become an accepted part of many cultures, as happened earlier with emailing. Reg #: 222 Student Name: David Area Name: Java Course ID: 141992 Col 1 #: 12 Faculty Name1: John Faculty Name2: TBD URL: www.urlname.com Other Code: 38738392983 |
Solution to find: from the Comments need to extract the data values as below –
Date Completion | Student No | Area Name | Col 1 # | Other Code |
10/20/2017 | AB1089 | Analytics | 35280 | 83476483 |
11/22/2018 | Z1089 | Data 22 | DS3843834343 | 48563 |
01/25/2018 | Za13439 | .Net | 2838473 | 4973 |
11/25/2017 | Z108222 | Sun Sol | 7565234 | 8347343234 |
05/15/2018 | BB31089 | Java | 12 | 38738392983 |
Just a tip of how the community works: everytime you have a new question, please raise a new topic. It keeps the community organized and easier for people to look for solutions when they need to.
It's a best practice to close a topic and mark as solved and raise a new topic when you have a new question. Also, you get more visibility from other peers that can help you out.
Anyway, here's the solution for your problem:
- Use Text to Columns in the Comments column with a "\n" delimiter and option Split to Rows
- Use another Text to Columns in the Comments column with a ":" delimiter and option Split to Columns (2 columns)
- Cross-Tab tool grouping by Sl No., with Comments1 as New Column Headers and Comments2 as Values for New Columns
- Data Cleansing Tool to get rid of trailing and leading whitespaces
- Select tool to keep only the columns you need and rename them due to underscores created by Cross-Tab tool.
Attached workflow on version 2018.4
Cheers,