How to create a new dimension split by Comma and count the value

[eddywong71]

My Dataset is like below.

Product

Apple,Orange

Apple

Orange

Orange, Banana

Banana

Apple,Orange,Banana

Then I create a new dimension : SubField([Product],',',1) as New Product. 

Product

Apple

Orange

Banana

May i ask what expression that i can use to count the product . I want the result as below.

Product	Count
Apple	3
Orange	4
Banana	3

[BrunPierre]

?

Dimension: New Product

Measure: Count(Product)

[eddywong71]

Hi BrunPierre,

Count(Product) can not have a correct result. The result will like

Product	Count
Apple	3
Orange	2
Banana	1

[marcus_sommer]

Your dimension isn't suitable because you just takes the first fruit. Therefore remove the third parameter of subfield() which then worked as an internal loop and creating a new record for each sub-part - just:

SubField([Product],',') as New Product

[eddywong71]

Can any one could help 🙏

[eddywong71]

It is because I want to select all the fruit from the Product to become in single dimension. The data allow a row can include more than one fruit. Is there way to distinct all the fruit as dimension. Then count the fruit.

The Data as below.

Buyer	Product
Sam	Apple,Orange
May	Apple
Mary	Orange
Tom	Orange, Banana
Andy	Banana
Peter	Apple,Orange,Banana

I want the result as below.

Product	Product Penetration
Apple	3/6
Orange	4/6
Banana	3/6

[Fabiano_Martino_Intelco · Accepted solution]

Hi @eddywong71 

following the instructions of @marcus_sommer i created this script:

Fruits:

Load * Inline [

Buyer; Product

Sam; Apple,Orange

May; Apple

Mary; Orange

Tom; Orange,Banana

Andy; Banana

Peter; Apple,Orange,Banana

](delimiter is ';')

;

Fruits_Expanded:

Load

Buyer,

SubField(Product, ',') as Fruit

Resident Fruits;

You can then count the Fruit to get the total of each fruit.

The Count of Total Distinct Buyer will get the base to measure the product penetration.

Regards

Fabiano

[eddywong71]

It works . Thanks so much !