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

Question

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.

InputOutputColumn 1Column 2Column 3Column 4Column 5Column 6ZZ 01  ***  QWERTY  ********* AAAAA  1234ZZ 01***QWERTY*********AAAAA1234ZZ 01 ***ZXCCVBN  ********* BBBBB 5678ZZ 01***ZXCCVBN*********BBBBB5678ZZ 01** QWDFGHJ********* CCCCC 0987ZZ 01**QWDFGHJ*********CCCCC0987DE01  *  ASKDLJFIGO  ********* DDDDD  1087DE01*ASKDLJFIGO*********DDDDD1087ZZ 01  ***LOIYGFBFUD********* EEEEE  0746ZZ 01***LOIYGFBFUD*********EEEEE0746BB 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 2OutputColumn 1Column 1Column 1123412340009567856789999129012901111878987892222785678563333230823084444 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 2Outputaaaaagggggaaaaabbbbbhhhhhbbbbbccccciiiiicccccdddddjjjjjdddddeeeeekkkkkeeeeeffffflllllfffff 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.

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

Please help me to solve these scenario's.

RolandSchubert · Answer

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

JoinProblems.yxmd

Jean-Balteryx · Answer

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.

Gsiva3 · Answer

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.