as a result of an inputlookup, I have the following table 1(a dish that a chef can prepare, and the chef's name):
dish | chef |
a | gordon ramsay |
b | gordon ramsay |
c | Guy Fieri |
d | Guy Fieri |
f | Jamie Oliver |
g | gordon ramsay |
h |
and I have the following table from another outputlookup (the restaurant where a chef works, and the chef name):
restaurant | chef |
1 | gordon ramsay |
2 | Guy Fieri |
3 | Guy Fieri |
4 | Jaime Oliver |
5 | Michael Caines |
I want to combine the 2 tables into this:
restaurant | dish | chef |
1 | a | gordon ramsay |
1 | b | gordon ramsay |
2 | c | Guy Fieri |
2 | d | Guy Fieri |
3 | c | Guy Fieri |
3 | d | Guy Fieri |
4 | f | Jamie Oliver |
1 | g | gordon ramsay |
1 | h | gordon ramsay |
5 | null | Michael Caines |
null | h | Rachael Ray |
Basically, based on tables 1 & 2, how do I get a table telling me the restaurant where a chef works, the dishes that he/she would prepare, and the chef's name?
In stuff I've tried, I'm able to combine table 1 & 2 with the join command, but a lot of results end up getting filtered out. (eg. I might end up with one result per chef but not getting all the dishes, or one result per dish but not getting all the restaurants).
Your spelling of Jamie Oliver in your restaurants is wrong - "Jaime" and there is no Rachel Ray in dishes for dish h.
However, this will given you the table - not exactly the same order
| inputlookup dishes.csv
| lookup restaurants.csv chef
| inputlookup append=t restaurants.csv
| stats values(*) as * by restaurant chef
| mvexpand dish
| table restaurant dish chef
this also, with ordering more as you have in your table
| inputlookup dishes.csv
| lookup restaurants.csv chef
| inputlookup append=t restaurants.csv
| mvexpand restaurant
| eventstats count(dish) as dishes by chef
| where dishes=0 OR isnotnull(dish)
| table restaurant dish chef dishes
Your spelling of Jamie Oliver in your restaurants is wrong - "Jaime" and there is no Rachel Ray in dishes for dish h.
However, this will given you the table - not exactly the same order
| inputlookup dishes.csv
| lookup restaurants.csv chef
| inputlookup append=t restaurants.csv
| stats values(*) as * by restaurant chef
| mvexpand dish
| table restaurant dish chef
this also, with ordering more as you have in your table
| inputlookup dishes.csv
| lookup restaurants.csv chef
| inputlookup append=t restaurants.csv
| mvexpand restaurant
| eventstats count(dish) as dishes by chef
| where dishes=0 OR isnotnull(dish)
| table restaurant dish chef dishes
just wanna say thanks. Your solution worked for my case 🙂