Extract the 2nd line and allocate the remaining

[Kallis]

I am new to Alteryx development. Appreciate every help that I get here.

Below are 2 excel tables. I need to locate the vendor no that is there in FP Data, in the excel, Triage Data. Once it is found,

a. I need to remove the particular row from the FP Data and also the very next row and write it into another excel. With the Join Tool, I       am able to remove the "particular row" but I do not know how to fetch the next line.

   I thought of Select tool but by having it before the Join tool, it will remove some lines before going into the Join Tool. If I include the        Select Tool after Join, by the time the records are already separated and Select Tool as such will not have any use.

b. The remaining rows, ie., not removed ones, must be allocated to the team members that is mentioned  under the table Owners. It           must also be equally allocated. If there are 5 members and if there are 50 lines, every member will have to be allocated 10 each.

TRIAGE DATA

Duplicate Group	Division	Vendor No.	Vendor	Internal Ref.	Invoice No.
1	Div 1	20000	Vendor 1	Ref 1	Invoice 1
2	Div2	30000	Vendor 2	Ref 1	Invoice 2
3	Div3	40000	Vendor 3	Ref 1	Invoice 3
4	Div4	50000	Vendor 4	Ref 1	Invoice 3
5	Div 1	55000	Vendor 5	Ref 1	Invoice 3
6	Div2	34000	Vendor 6	Ref 1	Invoice 3
7	Div3	53000	Vendor 7	Ref 1	Invoice 3
8	Div4	12000	Vendor 8	Ref 1	Invoice 3
9	Div 1	11000	Vendor 9	Ref 1	Invoice 3
10	Div2	29000	Vendor 10	Ref 1	Invoice 3
11	Div3	56000	Vendor 11	Ref 1	Invoice 3
12	Div4	77000	Vendor 12	Ref 1	Invoice 3
13	Div 1	45000	Vendor 34	Ref 1	Invoice 3
14	Div2	33000	Vendor 66	Ref 1	Invoice 3
15	Div3	36000	Vendor 76	Ref 1	Invoice 3

FP DATA

Risk	System	Division	Vendor No.	Vendor	Internal Ref.	Invoice No.
High	Sys1	Division 1	20000	Vendor 1	Ref 1	Invoice 1
High	sys2	Divison 2	30000	Vendor 2	Ref 1	Invoice 2
High	Sys1	Division 3	40000	Vendor 3	Ref 1	Invoice 3
High	sys2	Division 4	50000	Vendor 4	Ref 1	Invoice 3
High	Sys1	Division 5	55000	Vendor 5	Ref 1	Invoice 3
High	sys2	Division 5	34000	Vendor 6	Ref 1	Invoice 3
High	Sys1	Division 6	53000	Vendor 6	Ref 1	Invoice 3
High	sys2	Division 7	12000	Vendor 8	Ref 1	Invoice 3
High	Sys1	Division 7	11000	Vendor 9	Ref 1	Invoice 3
High	sys2	Division 8	29000	Vendor 10	Ref 1	Invoice 3
High	Sys1	Division 8	56000	Vendor 11	Ref 1	Invoice 3
High	sys2	Division 9	77000	Vendor 12	Ref 1	Invoice 3
High	sys5	Division 10	45000	Vendor  12	Ref 1	Invoice 3
High	sys8	Division 11	33000	Vendor 14	Ref 1	Invoice 3
High	sys9	Division 12	36000	Vendor 88	Ref 1	Invoice 3

OWNERS

Row Labels	Primary	Secondary
USA	Kate	Winslet
Switzerland	Samuel	Jackson
India	Kate	Winslet
Turkey	Samuel	Jackson
Germany	Brad	Pitt
France	Brad	Pitt
Mexico	Nicholas	Cage
Canada	Nicholas	Cage
Poland	Tom	Cruise
United Kingdom	Tom	Cruise
Pakistan	Kate	Winslet
China	Brad	Pitt
Belgium	Keanu	Reeves
Italy	Actor 1	Actor 2

[ed_hayter]

1a) Once you have the row that needs to be removed via join I would use an append tool to add that record back into data. i think a good idea would be to use a recordID so that you only have to append one field back onto the data.

Create a [filter field] in formula tool if recordID = Source_recordID = 1 else null ENDIF

Then multi-row formula when [row-1:filter field] = 1 then 2. 

Then filter tool to filter for [filter field] greater than or = to 1 then you will filter those two rows.

For b) if you count rows in both the FP table and owners table with two summarize tools. Append those counts to each other and divide the FP row count by owner count you get the value each owner needs to take [case count]. Append that value onto the owners table and use a generate rows to make a new field [case number] with a loop condition that [case number] <= [case count]. Then if assignment of cases does not matter you can join the rest of the data on record position to evenly distribute the cases.

Couldn't load your data into alteryx at this time so sorry for the wordy response

[Kallis]

Hi @ed_hayter

Thanks for the reply. I will try this solution. 

[Kallis]

Hi hayster 

I have attached a small workflow. Will you be able to let me know where do I have to add the tools that you have mentioned.

Thanks

Bobji

[OllieClarke]

@Kallis I'm struggling to understand exactly what you're after. Could you provide a sample output using the tables you set out above please?

[Kallis]

Hello @OllieClarke 

I will explain the first requirement. The 2nd one, I will come to that once this is solved.

In FP Data, under the column Vendor No, if Vendor No. 20000 is located in Triage Data, the entire 1 & 2 rows in FP Data must be sent to another excel. Likewise, in FP data, Vendor No. 40000 is located in Triage Data, the entire 3 & 4 rows must be sent to another excel. This is how the solution must proceed. If the vendor number is located in Triage data, that row and the very next row must be sent to another excel. Hope it is clear.

In the above WF, the first input is the Triage data and the 2nd one is the FP data.

FP DATA

SrNo	System	Division	Vendor No.	Vendor	Internal Ref.	Invoice No.
1	Sys1	Division 1	20000	Vendor 1	Ref 1	Invoice 1
2	sys2	Divison 2	30100	Vendor 2	Ref 1	Invoice 2
3	Sys1	Division 3	40000	Vendor 3	Ref 1	Invoice 3
4	sys2	Division 4	40100	Vendor 4	Ref 1	Invoice 3
5	Sys1	Division 5	55000	Vendor 5	Ref 1	Invoice 3
6	Sys2	Division  1	55100	Vendor 7	Ref 3	Invoice 8

TRIAGE DATA

Duplicate Group	Division	Vendor No.	Vendor	Internal Ref.	Invoice No.
1	Div 1	20000	Vendor 1	Ref 1	Invoice 1
2	Div3	40000	Vendor 3	Ref 1	Invoice 3
3	Div 1	55000	Vendor 5	Ref 1	Invoice 3

[Kallis]

Please, can someone help

[ed_hayter]

I've made a workflow for part 1. The stuff in containers is reshaping the data to look like how you gave it to us as it did not copy in correctly for me.

Also I used your second set of FP data and Triage Data as the original set all 15 records match so was a bit confused.

I have annotated the logic and at present just set the export to a new excel sheet for each instance of a vendorID match but that could be amended to be separate excel files.