Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
musketeers
Creator
Creator

Set analysis help need

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
Labels (2)
1 Solution

Accepted Solutions
vincent_ardiet_
Specialist
Specialist

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

View solution in original post

7 Replies
vincent_ardiet_
Specialist
Specialist

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

vincent_ardiet_
Specialist
Specialist

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

musketeers
Creator
Creator
Author

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_
Specialist
Specialist

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
Creator
Creator
Author

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_
Specialist
Specialist

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

musketeers
Creator
Creator
Author

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.