Hi All,
I am pretty new to Alteryx. I am trying to explore an option, if i can just search a keyword in the cell and match the data with different data set.
Example below.
This is my description.
DESCRIPTION |
03-Nov-2020 MARTINS/MARCO MR TORONTO PEARSON/VANCOUVER INTL/TORONTO PEARSON 106 |
08-Jan-2021 FLANAGAN/AISLING TERESA MS EDMONTON INTL/TORONTO PEARSON 16001 |
18-Feb-2021 ARAM/HEIDI MRS 205 |
06-Feb-2021 MBALE/JIBIKILA DR 1057 AND 1062 |
05-Feb-2021 PALMA LOPEZ/CESAR AUGUSTO MR 1057 1067 |
12-Feb-2021 TAZI/ABDELAZIZ MR 1028 |
17-Feb-2021 RAIS/SALIM DR 12001 |
17-Feb-2021 RAIS/SALIM DR MONTREAL DORVAL/TORONTO PEARSON/MONTREAL DORVAL 12001 |
01-Apr-2021 RAIS/SALIM DR 12005 |
29-Mar-2021 RAIS/SALIM DR MONTREAL DORVAL/CALGARY INTL/MONTREAL DORVAL 12005 |
26-Mar-2021 RAIS/SALIM DR 12004 |
26-Mar-2021 RAIS/SALIM DR MONTREAL DORVAL/TORONTO PEARSON/MONTREAL DORVAL 12004 |
04-Feb-2021 SIDDIQUI/SHAH MOHAMMED ZAIN MR 406 |
11-Feb-2021 SIDDIQUI/SHAH MOHAMMED ZAIN MR 1059 |
15-Feb-2021 SIDDIQUI/SHAH MOHAMMED ZAIN MR EDMONTON INTL/VANCOUVER INTL/EDMONTON INTL 07254 |
16-Feb-2021 SIDDIQUI/SHAH MOHAMMED ZAIN MR 07254 |
05-Feb-2021 JAGO/AOIFE ANNE MS 1216 |
05-Feb-2021 LAMARI/SOFIANE MR 124104 |
17-Feb-2021 MARUCA/BRIGIDA MS MONTREAL DORVAL/CALGARY INTL/EDMONTON INTL/CALGARY INTL/MONTREAL DORVAL 07003 |
21-Feb-2021 MARUCA/BRIGIDA MS 07003 |
07-Feb-2021 PALMA LOPEZ/CESAR AUGUSTO MR CALGARY INTL/EDMONTON INTL/CALGARY INTL 1057 1067 |
10-Feb-2021 PALMA LOPEZ/CESAR AUGUSTO MR 1057 1067 |
19-FEB-2021 PEPIN/NATHALIE MARIE MS Professional Fee NA |
10-Feb-2021 ARAM/HEIDI MRS 901 |
18-Feb-2021 CARMICHAEL/PETER MR 1077 AND 1024 |
19-Feb-2021 MOURANI/VIVIANE MS NA |
16-Feb-2021 MARTINS/MARCO MR TORONTO PEARSON/VANCOUVER INTL/TORONTO PEARSON 106 |
Name should be matching data and i need the target value against that.
NAME | TARGET |
MARTINS MARCO | 1 |
FLANAGAN AISLING TERESA | 2 |
ARAM HEIDI | 3 |
MBALE JIBIKILA | 4 |
TAZI ABDELAZIZ | 5 |
RAIS SALIM | 6 |
SIDDIQUI SHAH MOHAMMED ZAIN | 7 |
JAGO AOIFE ANNE | 8 |
LAMARI SOFIANE | 9 |
Also please be informed that, there isn't any definite pattern in the in the data set.
Thanks in advance.
Srinivasan Vishwanathan
Solved! Go to Solution.
Hi @vishwanathans,
I have prepared a workflow for you:
Output:
Find and replace is a tool to go but first, we need to use text to column to prepare the data and split it by rows.
Hi @vishwanathans,
If you need to check if all the words are present this is a little bit more robust solution:
Output:
In the previous solution, I have only checked if one of the worst from a second data set is present. In this one, I am checking if all the words are present.
Thanks Emil. You are true genius.
Can you also help me with this if you can.
Is there any way that i need to just carve out the details between 2nd _ and 3rd _ from the data below.
"212361058_840111_WARD/CHANDRA DANIELLE_2/4/2021_San Antonio"
Hi @vishwanathans,
I am not a genius... but I am very grateful for a kind word 😄
Propably the simplest solution is using text to column + select tool.