Calculate metrics over Distinct values of a dimension

[Anonymous]

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.

[YoussefBelloum]

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 ??

[swuehl · Accepted solution]

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.

[Sergey_Shuklin]

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;

[Saravanan_Desingh]

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 '|');

[Anonymous]

Thank you all for taking the time to reply! swuehl‌ your answer worked for me!

It was really helpful.