Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I'd like to compare 2 tables using 2 columns Engine Model & Keyword to see which and how many Vendors can repair certain engines or when a keyword is chosen, which and how many Vendors show up.
Here is my data:
1. "Data" table (contains only 1 Vendor name. With the engines and keywords this Vendor has, how many Vendors in the full updated ALL PARTS table can repair the same engine or show the same keyword and who are they)
Vendor Name | Part Received | Net Order Cost | Engine Model | Keyword |
Ha | 822822-4-005 | $489.21 | W500 | EEC |
Ha | 822822-4-005 | $562.23 | W500 | EEC |
Ha | 819300-9-009 | $578.55 | W500 | EEC |
Ha | 822822-4-005 | $814.00 | W500 | EDU |
Ha | 822822-4-005 | $814.00 | W500 | EDU |
Ha | 822822-4-005 | $814.00 | W500 | EEC |
Ha | 819710-2 | $814.00 | W200 | EEC |
Ha | 822825-7-009 | $945.05 | W500 | VALVE |
Ha | 819300-9-009 | $1,000.00 | W500 | FUEL CONTROL |
Ha | 817962-8-012 | $1,215.00 | W500 | DIVIDER |
Ha | 822825-7-009 | $1,215.00 | P6 | FUEL CONTROL |
Ha | 822825-7-009 | $1,215.00 | W300 | EEC |
Ha | 025277-300-08 | $1,215.00 | W500 | FUEL CONTROL |
Ha | 822825-6-009 | $1,215.00 | W200 | DIVIDER |
2. full updated ALL PARTS table (has all vendor names and their corresponding data)
Vendor Name | Part Received | Net Order Cost | Engine Model | Keyword |
AMETEK | 91020-000-4 | $22,885.00 | W150 | EEC |
AMETEK | 91020-000-4 | $2,382.00 | W150 | EEC |
AMETEK | 91020-000-4 | $1,629.00 | W500 | EEC |
AMETEK | 3044926-01 | $2,389.00 | W500 | EEC |
ATECH | 3122633-01 | $22,885.00 | W500 | FUEL CONTROL |
ATECH | 3072344-01SB35301 | $21,830.00 | W500 | DIVIDER |
ATECH | 3073555-01 | $18,200.00 | W500 | FUEL CONTROL |
ATECH | 3072344-01SB35301 | $2,389.00 | P6 | FUEL CONTROL |
BUDNEY | 3108467-01 | $22,885.00 | W500 | FUEL CONTROL |
BUDNEY | 3052959-01 | $2,547.96 | P6 | FUEL CONTROL |
BUDNEY | 3024121 | $6,777.00 | W300 | EEC |
BUDNEY | 3048972-01 | $9,146.49 | P6 | FUEL CONTROL |
BUDNEY | 30J2411-01 | $22,885.00 | W300 | EEC |
BUDNEY | 3123769-01 | $15,680.09 | W300 | PMU |
CHAMPION | 3048972-01 | CH92043-3 | P6 | EEC |
CHAMPION | 3045846-01 | CH92051-4 | W300 | EEC |
CHAMPION | 3045846-01 | CH92043-3 | W300 | EEC |
CHAMPION | 06R30J2078-01 | CH92043-3 | W300 | EEC |
CHAMPION | 06R30J2078-01 | CH92043-3 | W300 | EEC |
CHROMA | 4503375266 | 3106862-01 | W300 | EEC |
CHROMA | 4503375266 | 3118471-01 | P6 | EEC |
CHROMA | 4503377261 | 3121253-01 | W300 | EEC |
CHROMA | 4503366757 | $6,777.00 | W300 | EEC |
Ha | 809575-2-004 | $18,200.00 | W500 | EEC |
Ha | 766859F | $6,914.00 | W500 | EDU |
Ha | 822835-5-108 | $6,777.00 | W500 | EDU |
Ha | 819300-9-009 | $6,321.00 | W300 | EDU |
CRANE | 9-361-06 | $7,073.11 | W300 | EDU |
CRANE | 9-361-06 | $14,482.70 | W300 | EDU |
Ex:
Using Engine Model from Data table as a slicer, when W500 is selected, how many alternative vendors from the full updated ALL PARTS table can repair that same engine other than Vendor Ha and who are they?
Desired results should show a list of the following: AMETEK, ATECH, BUDNEY, instead of showing: AMETEK, ATECH, BUDNEY, Ha. Meaning Ha should be excluded in the list.
How would you approach my problem?
Thank you so much and please help a newbie out!
Just an idea on top of my mind:
This way you will produce a table, that is essentially containing only the "alternative" vendors, as it excluded the vendor for that particular engine/keyword, which is in the first table. And at the same time it is related to the Data table, so you can have a primary vendor (coming from the Data table) and list of all Alternative ones, using the relationship.
HTH, Ivan
Hi @Anonymous, can you please explain what you meant by creating a custom column that concatenates Vendor Name, Engine Model & Keyword? Sorry I'm not really familiar with all this stuff. Thanks!
In Query Editor, add a Custom Column with the following formula:
[Column 1] & [Column 2] & [Column 3]
Just replace [Column 1], [Column2], etc. with your actual columns. The result for step 1 should be "HaW500EEC" for the first row in the Data table.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |