Set analysis help need

[musketeers]

Hi, I have a scenario. There are few Products and few Codes. Codes are related to few Products but its not necessary that Those products will be related to these codes. For example Code C1 is related to products P1, P2, P3 and P4. But its not necessary that in Product table we have Code information for all instances of Products. Example of data is like:

Product ID	Code ID	Product Value
P1	C1	100
P2	C1	101
P3	C1	102
P4	C1	103
P1		104
P2		105
P1		106
P8		107
P10		108
P10	C2	109
P11	C2	110
P12	C2	111
P13	C2	112
P14	C2	113
P14		114
P12		115
P18		116
P18	C3	117
P19	C3	118
P20	C3	119

CODE ID	Code Desc
C1	CODE DESC 1
C2	CODE DESC 2
C3	CODE DESC 3

ANd I need output like below:

Code Desc	Total Volume
CODE DESC 1	721
CODE DESC 2	892
CODE DESC 3	470

Its like: For Code Desc 1 we are getting Total volume 721. Explanation: For Code Desc 1 we have Products P1, P2, P3 and P4. Now in Product table we have to sum the volume of all P1, P2, P3 and P4 which gives us 721.

For Total Volume I am writing like:

SUM({<Product ID=P(Product ID),CODE ID=,Code Desc=>}Product Value)

This give me below which is wrong:

Code Desc	Total Volume
CODE DESC 1	406
	315
CODE DESC 2	555
	337
CODE DESC 3	354
	116

[vincent_ardiet_]

Do you have access to the loading script? This will be easier and cleaner to fix the data there.

[vincent_ardiet_]

If this is not the case you can try this one:
sum(aggr(nodistinct sum([Product Value]),[Product ID]))

[musketeers]

Hi Vincent, Sorry for this. I should have provided in my question:

Below is the inline table script:

Product:
load * Inline[
Product_ID, Code_ID, Product_Value
P1, C1, 100
P2, C1, 101
P3, C1, 102
P4, C1, 103
P1, , 104
P2, , 105
P1, , 106
P8, , 107
P10, , 108
P10,C2, 109
P11,C2, 110
P12 ,C2,111
P13,C2, 112
P14,C2, 113
P14, , 114
P12, , 115
P18, , 116
P18,C3, 117
P19,C3, 118
P20,C3, 119
];

Code_Detail:
Load * inline [
Code_ID,Code_Desc
C1, CODE DESC 1
C2, CODE DESC 2
C3, CODE DESC 3

];

Hope this helps.

[vincent_ardiet_]

So either you use this expression in your chart:
sum(aggr(distinct sum([Product Value]),[Product ID]))

Either you modify you script adding this:

tmpProductCode:
Load [Product_ID],MaxString([Code_ID]) as [Code_ID] Resident Product Group by [Product_ID];

Drop Field [Code_ID] from Product;

Inner Join (Product) Load [Product_ID],[Code_ID] Resident tmpProductCode;

Drop Table tmpProductCode;

[musketeers]

Hi Vincent, The expression sum(aggr(distinct sum([Product Value]),[Product ID])), is not giving correct result:

But the second approach is giving correct result.

But still wanted to know if we can do it in Set analysis instead of backend table change.

[vincent_ardiet_ · Accepted solution]

Sorry, I did a mistake the second time, the expression is:
sum(aggr(nodistinct sum([Product Value]),[Product ID]))

[musketeers]

Hi Vincent,

Thanks for your help. Your suggestions has lead me to a different expression which is working in my real scenario:

Sum(total<PRODUCT_ID> TOTAL_VOLUME)

The expression you have suggested was not giving wrong number but it was multiplying the counts where products are duplicate.

For example let say for case id 1 we have product id 1 is repeated 4 times then the sum of distinct total volume was multiplied 4 times.

Thanks for your helps. It was much needed as I was working this issues from past one week.