Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Multi Row Question

Emil_Kos
17 - Castor
17 - Castor

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_idServer IDrecipient
11111111111Server 1RecipientA,RecipientB
11111111112Server 1RecipientB,RecepientC
11111111113Server 1RecepientA,RecepientB
11111111114Server 1RecepientB
11111111115Server 1RecepientC

 

This is desired output:

 

request_idServer IDrecipientOutput/Chain
11111111111Server 1RecipientA,RecipientBServer 1
11111111112Server 1RecipientB,RecepientCServer 1
11111111113Server 1RecepientA,RecepientBServer 1
11111111114Server 1RecepientBServer 1
11111111115Server 1RecepientC

 

 

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. 

16 REPLIES 16
atcodedog05
22 - Nova
22 - Nova

Hi @Qiu & @Emil_Kos 

 

I built a lengthy workflow considering there might be a scenarios with more then 2 recipients😅 

Emil_Kos
17 - Castor
17 - Castor

Hi @atcodedog05,

 

Actually there is a scenario like this. 

 

I think there might be 3 recipients at most at the same time.

 

I need also to think about the efficiency because the amount of rows that I got is quite big and the data set have couple more columns that I need to work with. 

atcodedog05
22 - Nova
22 - Nova

Hi @Emil_Kos 

 

If you know what is the at most recipients. I think @Qiu 's method is much preferable 😅

Qiu
20 - Arcturus
20 - Arcturus

@Emil_Kos 
Thank you for the feedback.

With our little comments, I believe you are already good to go and good luck with you workflow.

Emil_Kos
17 - Castor
17 - Castor

Hi @atcodedog05 @PhilipMannering @Qiu,

 

Thank you for your help and it took me while but actually I found really simple way to solve my problem.

 

After using text to columns and splinting the data to rows I needed to sort it in a right order.

Afterwards using multi row formula allowed me to get the proper result. 

 

Thank you for your help!

Qiu
20 - Arcturus
20 - Arcturus

@Emil_Kos 

Good to know you get all sorted out.

 

atcodedog05
22 - Nova
22 - Nova

Glad to know you sorted it out @Emil_Kos 🙂

Labels