Hi, I am new to Alteryx development. I would like to request for help.
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, I need to remove the particular row from the FP Data and write it into another excel. Please let me know how can I prepare the workflow
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
|
|
Solved! Go to Solution.
@Kallis You can join the two datasets on Vendor No. Any records from FP data that don't join will come out of the Join tool. You can take these records and write them to another Excel file, thus "removing" the records where the Vendor No. in FP is in the Triage data file.
I would use thé Join tool to connect them via the Vendor No. as the primary key for both tables. If they appear in J, then you’ve already technically removed them from the FP data since the Join tool works with L, J, and R outputs.
I would assume the Triage Data is the left side and the FP data is the right side. So whatever comes out of the right side is whatever that is not found in the Triage data.
Thanks. Let me try this solution
Thanks. Let me try
Thanks Prometheus and Caltang. The solution works. I would like to format the spreadsheet before it is written in the output folder. I know it needs a combination of tools like Render, Layout. Can you please let me know how this can be achieved.
Let us know how you want it to look like.
cc @Prometheus
I want the final output to look like format as table in Excel. Font to be Arial Nova and the size set to 9.
There is also one other requirement. From the table FB data, I need to remove the very next line alongwith the line that has been located in the Triage table. Please help.
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 Select Tool after Join, by the time the records are already separated and Select Tool as such will not have any use.