Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How to join files which does not have common data between them

Gsiva3
8 - Asteroid

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.

 

InputOutput
Column 1Column 2Column 3Column 4Column 5Column 6
ZZ 01  ***  QWERTY  ********* AAAAA  1234ZZ 01***QWERTY*********AAAAA1234
ZZ 01 ***ZXCCVBN  ********* BBBBB 5678ZZ 01***ZXCCVBN*********BBBBB5678
ZZ 01** QWDFGHJ********* CCCCC 0987ZZ 01**QWDFGHJ*********CCCCC0987
DE01  *  ASKDLJFIGO  ********* DDDDD  1087DE01*ASKDLJFIGO*********DDDDD1087
ZZ 01  ***LOIYGFBFUD********* EEEEE  0746ZZ 01***LOIYGFBFUD*********EEEEE0746
BB 01 **** DDDRRRRTTYYUUU  *************************    1456BB 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 1Input 2Output
Column 1Column 1Column 1
123412340009
567856789999
129012901111
878987892222
785678563333
230823084444
 00095555
 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 1Input 2Output
aaaaagggggaaaaa
bbbbbhhhhhbbbbb
ccccciiiiiccccc
dddddjjjjjddddd
eeeeekkkkkeeeee
ffffflllllfffff
 mmmmmggggg
  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.

 

InputOutput
AAAA********NNN***88 1122QQ 01********** Start of the Task*************
BBB*****FFF**887586ASDSSDLK*************12323
CCCC*****8FDFDFR***ADWDEFGKI*****09884
VF 
GB 
HN 
FV 
GB 
QQ 01********** Start of the Task************* 
ASDSSDLK*************12323 
FGKI*****09884 

 

 

Please help me to solve these scenario's.

4 REPLIES 4
Jean-Balteryx
14 - Magnetar

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).

Gsiva3
8 - Asteroid

Hi @Jean-Balteryx 

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.

Jean-Balteryx
14 - Magnetar

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.

RolandSchubert
15 - Aurora
15 - Aurora

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

Labels