Forum Discussion
Recipe nutrition database/pivot table
I will try to get that when I get home. So far the table is relatively incomplete while I tried to figure it out. Thank you for trying to help.
- It took me awhile to figure out how to send the link, but hopefully it worked. I do use SUM quite a bit, but nesting the filter within SUM Im unsure of. Unless you are just saying to do this
=SUM(Filter)- I can see that I will need to remove any letters from the numbers for each nutritional value. Now I just have to figure out how to add each recipe to the pivot table so that I can then filter it by meals.
Another thought regarding the design of your database (assuming that I'm right, that what you've done so far in that demo file was just a single recipe for a single dish),
Add a first column that names the dish. Copy the same name to each row of the ingredients. And do the same for each dish in your repertoire. You could then easily select the dishes to be served and get the totals of all the various nutritional dimensions for each of the ingredients for any combination of dishes.
- https://1drv.ms/x/c/5c44253842f5d23a/EfAAk3CHyc1NstyviJj9K9sBf9Xvz2poWgu8Uiw8xrMalg?e=EsS0CC
Hi,
select * from nutritionData limit 20;
select * from recipesData;
cli_one_dim~nutritionData~2;
//select * from nutritionDataunion;
create temp table aa as
select Ingredient,属性,数量,cast(数量 as float) qty,regexp2('\D$',数量) unit from nutritionDataunion;
select * from aa;
create temp table bb as
select * from recipesData join aa on Ingredient like f02;
select * from bb;
create temp table cc as
select f01,属性,sum(qty*f03)||unit total from bb group by f01,属性;
select * from cc;
cli_build_two_dim~cc~属性~total;
select * from cc_two_dim;