Question: Roll up/Aggregate sales

[Anonymous]

Hi, I need some help on trying to aggregate sales through product hierarchy.

I have four types of product, and these products group into 2 different types of product groups. (see below)

If i want to find out how much Class 1 sales in month of Jan, does anyone know what the formula would be?

Thanks,

Product	Month	Sales ($)
Product A	Jan	500
Product B	Jan	200
Product C	Feb	400
Product D	Jan	800
Product A	Feb	550

Product	Product Group
Product A	Class 1
Product B	Class 1
Product C	Class 2
Product D	Class 2

[vamsee · Accepted solution]

Try the following

Step1 --> Create a Map from Group Table with Product as Key and Group as Value

Step 2 --> Applymap Group onto Sales Table

Step 3 --> Perform Aggregation on Sales, in Sales table at Group and Month Level.

Group_Map:

Mapping Load Product, [Product Group];

LOAD * INLINE [

    Product, Product Group

    Product A, Class 1

    Product B, Class 1

    Product C, Class 2

    Product D, Class 2

];

Source:

LOAD *,

ApplyMap('Group_Map', Product,NULL()) as Group;

LOAD * INLINE [

    Product, Month, Sales ($)

    Product A, Jan, 500

    Product B, Jan, 200

    Product C, Feb, 400

    Product D, Jan, 800

    Product A, Feb, 550

];

Aggregated_Table:

LOAD

Group,

Month,

Sum([Sales ($)]) as Sales

Resident Source

Group By

Group,

Month;

DROP Table Source;