Conditional While Statement Based on Total No of Fields

[reddyd3]

Hi, 

I've a dataset with three fields  ID, Team , Order Number.

Now I am trying to build a 'case flow' based on how the 'Team' changes for each ID.

Below is the sample data 

ID	Team	Order Number
5005b00001ISpPKAA1		1
5005b00001ISpPKAA1	Network	2
5005b00001ISpPKAA1	PDS	3
5005b00001ISpPKAA1	PDS	4
5005b00001ISpPKAA1	PDIU	5
5005b00001ISpPKAA1	PDIU	6
5005b00001ISpPKAA1	PDS	7
5005b00001ISpPKAA1	Network	8
5005b00001ISpPKAA1	PDS	9
5005b00001ISpPKAA1	PDIU	10
5005b00001ISpPKAA1	PDIU	11
5005b00001ISpPKAA1	PDS	12
5005b00001ISpPKAA1	Network	13
5005b00001ISpPKAA1	PDS	14
5005b00001ItbDKAAZ		1
5005b00001ItbDKAAZ	Network	2
5005b00001ItbDKAAZ	PDS	3
5005b00001ItbDKAAZ	PDS	4
5005b00001ItbDKAAZ	PDS	5
5005b00001ItbDKAAZ	PDS	6
5005b00001ItbDKAAZ	Network	7
5005b00001ItbDKAAZ	PDS	8
5005b00001ItbDKAAZ	Network	9
5005b00001ItbDKAAZ	PDS	10

By using a cross tab  I was able to transform data as below.

ID

Max_Order Number

1

2

3

4

5

6

7

8

9

10

11

12

13

14

5005b00001ISpPKAA1

14

Network

PDS

PDS

PDIU

PDIU

PDS

Network

PDS

PDIU

PDIU

PDS

Network

PDS

5005b00001ItbDKAAZ

10

Network

PDS

PDS

PDS

PDS

Network

PDS

Network

PDS

5005b00001JxBz9AAF

8

Network

PDS

PDS

Network

PDS

Network

PDS

My question is I need write a condition to show the flow for each ID only when the team in the subsequent field changes. 

The number of fields for Each Id is shown by Max Order Number.

Below is the expected Outcome

ID	Max_Order Number	Case Flow
5005b00001ISpPKAA1	14	Network-PDS-PDIU-PDS-Network-PDS-PDIU-PDS-Network
5005b00001ItbDKAAZ	10	Network-PDS-Network-PDS-Network-PDS
5005b00001JxBz9AAF	8	Network-PDS-Network-PDS-Network-PDS

I have uploaded the data in the file. Any help is much appreciated .

Thank you,

Digvijay

[binuacs]

@reddyd3 

[reddyd3]

Hi Binuacs, 

Thank you very much for sharing the solution.

You see the condition is that if the same team appear in subsequent rows that value should be skipped until the team changes, so when you see PDS and PDS appearing in the two consequent rows it should not repeat the team value. 

So even though we have 10 rows for the second ID,

ID	Max_Order Number	1	2	3	4	5	6	7	8	9	10	11	12	13	14
5005b00001ItbDKAAZ	10		Network	PDS	PDS	PDS	PDS	Network	PDS	Network	PDS

we should have below outcome:

ID	Max_Order Number	Case Flow
5005b00001ItbDKAAZ	10	Network-PDS-Network-PDS-Network-PDS

Is there anyway we can do that.

thank you,

DJ

[Sebastiaandb · Accepted solution]

Hi @reddyd3 ,

I took @binuacs workflow and altered it (so credits for him, thanks!)

Greetings,

Seb

[reddyd3 · Accepted solution]

Hi Sebastiandb,

Can you please attach the alteryx workbook. 

Thank you so much for the solution.  This helps a lot for my analysis. :-)

Digvijay

[binuacs]

@reddyd3 

[reddyd3]

Thank you!!