Hi Team,
Hi @Luke_C and @Qiu @AngelosPachis
I have 4 requirements.
1) Want to segregate row values into multiple column values which are not in the same pattern. Attached below the input & expected output.
Input | Output | |||||
Column 1 | Column 2 | Column 3 | Column 4 | Column 5 | Column 6 | |
ZZ 01 *** QWERTY ********* AAAAA 1234 | ZZ 01 | *** | QWERTY | ********* | AAAAA | 1234 |
ZZ 01 ***ZXCCVBN ********* BBBBB 5678 | ZZ 01 | *** | ZXCCVBN | ********* | BBBBB | 5678 |
ZZ 01** QWDFGHJ********* CCCCC 0987 | ZZ 01 | ** | QWDFGHJ | ********* | CCCCC | 0987 |
DE01 * ASKDLJFIGO ********* DDDDD 1087 | DE01 | * | ASKDLJFIGO | ********* | DDDDD | 1087 |
ZZ 01 ***LOIYGFBFUD********* EEEEE 0746 | ZZ 01 | *** | LOIYGFBFUD | ********* | EEEEE | 0746 |
BB 01 **** DDDRRRRTTYYUUU ************************* 1456 | BB 01 | **** | DDDRRRRTTYYUUU | ************************* | 1456 |
2) Need to compare 2 files and throw output of the cells which are not duplicated.. I have attached input and sample output below
Input 1 | Input 2 | Output |
Column 1 | Column 1 | Column 1 |
1234 | 1234 | 0009 |
5678 | 5678 | 9999 |
1290 | 1290 | 1111 |
8789 | 8789 | 2222 |
7856 | 7856 | 3333 |
2308 | 2308 | 4444 |
0009 | 5555 | |
9999 | ||
1111 | ||
2222 | ||
3333 | ||
4444 | ||
5555 |
3) Need to join 2 input files, but these 2 files does not have any common values or columns between them. I have attached input and sample output below
Input 1 | Input 2 | Output |
aaaaa | ggggg | aaaaa |
bbbbb | hhhhh | bbbbb |
ccccc | iiiii | ccccc |
ddddd | jjjjj | ddddd |
eeeee | kkkkk | eeeee |
fffff | lllll | fffff |
mmmmm | ggggg | |
hhhhh | ||
iiiii | ||
jjjjj | ||
kkkkk | ||
lllll | ||
mmmmm |
4) I have a file with around 2000 line items, but I want to take input from the line which contains a unique value has "QQ 01********** Start of the Task*************". Is it possible to do this so. Attached input and sample output below.
Input | Output |
AAAA********NNN***88 1122 | QQ 01********** Start of the Task************* |
BBB*****FFF**887586 | ASDSSDLK*************12323 |
CCCC*****8FDFDFR***ADWDE | FGKI*****09884 |
VF | |
GB | |
HN | |
FV | |
GB | |
QQ 01********** Start of the Task************* | |
ASDSSDLK*************12323 | |
FGKI*****09884 |
Please help me to solve these scenario's.
Hi @Gsiva3 ,
Here are ideas for your scenarii :
1. Using REGEX you can break your string into the pieces you want. Look at the Regex tool documentation : https://help.alteryx.com/current/designer/regex-tool
2. You need to join both your columns and make a union of both L and R outputs so you retrieve everything that is only in one column (not duplicated).
3. I think a simple union should do the job, it will vertically assemble your data.
4. This one is a bit trickier, you should use the Multi-Row formula tool to add an identifier to each row, either to keep it (1) or remove it (0) then filter rows marked with the remove value (0).
Thanks for your reply.
I am yet to learn about regex.
So could you please provide the workflow for all the 4 scenario's which would be very helpful.
Here is a REGEX you can use for first scenario : (\u{2} ?\d{2}) *(\*+) *(\u+) *(\*+) *(\u+)? *(\d+)
As for the rest, you can give it a try, then send what you did if it doesn't work as expected.
Hi @Gsiva3 ,
there are different solutions for your problems.
1) You can split the string using formulas in multiple steps (there are functions to find specific characters or characters different to a specific character), I've attached a sample
2) It's a combination of "Joni" and "Union unjoined" - see attached workflow
3) You can use a Union tool to solve
4) Add a RecordID, find the recordid of the search string and filter only rows with higher record id (see attached workflow)
Let me know if it works for you.
Best,
Roland