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

Extract the 2nd line and allocate the remaining

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

Developer

Developer Tools

Output

Data Investigation

Accepted answers

All comments

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.

Export Grouped Reports.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703