Alteryx Designer Desktop Discussions

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

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
16 - Nebula
16 - Nebula

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
16 - Nebula
16 - Nebula

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
16 - Nebula
16 - Nebula

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