topic Re: How to create a new dimension split by Comma and count the value in App Development

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

eddywong71 — Thu, 20 Jun 2024 07:38:33 GMT

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

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

BrunPierre — Thu, 20 Jun 2024 09:20:24 GMT

Dimension: New Product

Measure: Count(Product)

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

eddywong71 — Thu, 20 Jun 2024 08:41:48 GMT

Hi BrunPierre,

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

Product	Count
Apple	3
Orange	2
Banana	1

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

marcus_sommer — Thu, 20 Jun 2024 10:25:20 GMT

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

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

eddywong71 — Thu, 20 Jun 2024 15:14:17 GMT

Can any one could help 🙏

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

eddywong71 — Thu, 20 Jun 2024 15:23:54 GMT

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

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

Fabiano_Martino_Intelco — Thu, 20 Jun 2024 15:45:49 GMT

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

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

eddywong71 — Fri, 21 Jun 2024 01:20:49 GMT

It works . Thanks so much !