Multi Row Question

[Emil_Kos]

Hi everyone,

Usually I am trying to answer questions of people but I am stuck and I am not sure how to move forward. 

I have a table with 20m+ rows and I am looking to identify if each line is in the chain. In order to do so at least one recipient that is the same for the previous row(comma is as a separator).

This is the data set: 

request_id	Server ID	recipient
11111111111	Server 1	RecipientA,RecipientB
11111111112	Server 1	RecipientB,RecepientC
11111111113	Server 1	RecepientA,RecepientB
11111111114	Server 1	RecepientB
11111111115	Server 1	RecepientC

This is desired output:

request_id	Server ID	recipient	Output/Chain
11111111111	Server 1	RecipientA,RecipientB	Server 1
11111111112	Server 1	RecipientB,RecepientC	Server 1
11111111113	Server 1	RecepientA,RecepientB	Server 1
11111111114	Server 1	RecepientB	Server 1
11111111115	Server 1	RecepientC

I wanted to use multi row formula:

IF [Server ID]=[Row-1:Server ID] AND
[recipient]=[Row-1:recipient]
Then [Server ID] Else '' ENDIF

But it will not work in my examples. 

I wanted to use text to columns but because recipient is not always in the same order or I can have couple different recipients for each line I don't think it will be a best solution.

Please advise me with some suggestions. I am sure the answer is quite easy but I am missing something obvious. 

[Qiu]

@Emil_Kos 
How do you define "Chain"?

And 'RecipientA' and 'RecepientA' are different?

[PhilipMannering · Accepted solution]

Not sure on the exact solution, but I strongly suggest you check out the Make Group Tool. So I would split to rows the [Recipient] and then use the Make Group tool with ServerID and Recipient fields. This should add a Group that identifies a member of a chain. Think it would be perfect for what you're after...

Thanks,

Philip

[Emil_Kos]

Hi @Qiu,

I tried to explain it in my initial post:

I have a table with 20m+ rows and I am looking to identify if each line is in the chain. In order to do so at least one recipient that is the same for the previous row(comma is as a separator).

So overall I am looking at the recipient column and at least one of them needs to be in the previous line. 

request_id	Server ID	recipient	Output/Chain	Just for Reference
11111111111	Server 1	RecipientA,RecipientB
11111111112	Server 1	RecipientB,RecepientC	Server 1	RecipientB
11111111113	Server 1	RecepientA,RecepientB	Server 1	RecipientB
11111111114	Server 1	RecepientB	Server 1	RecipientB
11111111115	Server 1	RecepientC

Actually I found a mistake in my initial post and the first line will not be in chain. 

[Qiu]

@Emil_Kos 
Multi Row Tool might the solution here.

But Can I ask why Row 3 is having Server ID if Row 1 doest not?

[atcodedog05]

Hi @Emil_Kos 

Here is a workflow for the task.

Output:

Workflow:

And it should be able to handle multiple recipients too.

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Qiu]

@atcodedog05 

There are 'RecipientA' and 'RecepientA' in the original post.

[atcodedog05]

@Qiu 

Yup i cleaned up the spelling guessing it was by mistake.

[Qiu]

@Emil_Kos 
if it is really just a typo, I get the same output as yours.

[Emil_Kos]

Hi @PhilipMannering,

I will learn about it later but a moment ago I wasn't able to make it work. 

@Qiu 

The first line shouldn't be marked because we don't have a previous line.

Recipient is a spieling mistake. 

I wasn't aware contains function can be used on few columns at the same time. Thank you for sharing!

@Qiu @atcodedog05 

I will check if those solutions work for me. In case I will have more questions I will get back to you.

I need to test it on original data.