Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a dimension and a metric, Ingredients and Weight, which have values as follow:
Ingredients | Weight
Butter, Soda, Flour | 100
Butter, Soda, Butter, Soda, Butter | 200
Fruit,Butter, Soda | 100
Butter,Soda | 50
Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour | 100
What I would really like to finally have is:
Ingredients | Weight
Butter, Soda, Flour | 200
Butter, Soda | 250
Fruit,Butter, Soda | 100
Any help is appreciated.
Thank you.
I take care of your requirement in the script:
LOAD Concat(DISTINCT Ingredient,', ') as Ingredients, Weight
GROUP by RecID, Weight;
LOAD
Trim(Subfield(Ingredients,',')) as Ingredient, Weight, RecID;
LOAD recno() as RecID, * INLINE [
Ingredients | Weight
Butter, Soda, Flour | 100
Butter, Soda, Butter, Soda, Butter | 200
Fruit,Butter, Soda | 100
Butter,Soda | 50
Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour | 100
] (delimiter is '|');
Now your Ingredients field shows only distinct Ingredient values, comma separated.
You can also just use
LOAD
Trim(Subfield(Ingredients,',')) as Ingredient, Weight, RecID;
LOAD recno() as RecID, * INLINE [
Ingredients | Weight
Butter, Soda, Flour | 100
Butter, Soda, Butter, Soda, Butter | 200
Fruit,Butter, Soda | 100
Butter,Soda | 50
Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour | 100
] (delimiter is '|');
And work on the single Ingredient list in your application.
Hi,
How want you have this line ??
Butter, Soda | 250
because on the first table you have:
Butter, Soda, Flour | 100
Butter, Soda, Butter, Soda, Butter | 200
Butter,Soda | 50
Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour | 100
So, you will have : 450 for Butter,Soda !
I'm I wrong ??
I take care of your requirement in the script:
LOAD Concat(DISTINCT Ingredient,', ') as Ingredients, Weight
GROUP by RecID, Weight;
LOAD
Trim(Subfield(Ingredients,',')) as Ingredient, Weight, RecID;
LOAD recno() as RecID, * INLINE [
Ingredients | Weight
Butter, Soda, Flour | 100
Butter, Soda, Butter, Soda, Butter | 200
Fruit,Butter, Soda | 100
Butter,Soda | 50
Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour | 100
] (delimiter is '|');
Now your Ingredients field shows only distinct Ingredient values, comma separated.
You can also just use
LOAD
Trim(Subfield(Ingredients,',')) as Ingredient, Weight, RecID;
LOAD recno() as RecID, * INLINE [
Ingredients | Weight
Butter, Soda, Flour | 100
Butter, Soda, Butter, Soda, Butter | 200
Fruit,Butter, Soda | 100
Butter,Soda | 50
Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour | 100
] (delimiter is '|');
And work on the single Ingredient list in your application.
Hello, Ismini!
To get exactly what you want you should use this script:
temp:
LOAD rowno() as row_num, Ingredients, Weight Inline
[Ingredients | Weight
Butter, Soda, Flour | 100
Butter, Soda, Butter, Soda, Butter | 200
Fruit,Butter, Soda | 100
Butter,Soda | 50
Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour | 100
]
(delimiter is '|');
temp_1:
LOAD row_num, trim(SubField(Ingredients,',')) as Ingr
Resident temp;
NoConcatenate
temp_2:
LOAD row_num, Concat(distinct Ingr,',') as Ingr Resident temp_1 Group by row_num;
DROP Table temp_1;
Left Join(temp_2)
LOAD row_num, Weight Resident temp;
DROP Table temp;
result:
LOAD Ingr as Ingredients, sum(Weight) as Weight Resident temp_2 Group by Ingr;
DROP Table temp_2;
Just adding one more preceding load on swuehl code
LOAD Ingredients, Sum(Weight) As Weights
GROUP by Ingredients;
LOAD Concat(DISTINCT Ingredient,', ') as Ingredients, Weight
GROUP by RecID, Weight;
LOAD
Trim(Subfield(Ingredients,',')) as Ingredient, Weight, RecID;
LOAD recno() as RecID, * INLINE [
Ingredients | Weight
Butter, Soda, Flour | 100
Butter, Soda, Butter, Soda, Butter | 200
Fruit,Butter, Soda | 100
Butter,Soda | 50
Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour,Butter,Soda,Flour | 100
] (delimiter is '|');
Thank you all for taking the time to reply! swuehl your answer worked for me!
It was really helpful.