topic Re: How to aggregate base on unique field in QlikView

How to aggregate base on unique field

rotero — Mon, 17 Oct 2016 03:05:07 GMT

hi i have this kind of table:

i would like to display in pivot table the promo amt per cv_number and also display the sum(amount) per product.

Let assume i have a thousand lines with promo_amt (which is referring to amt per cv_number) and we have also the per product charging of this per cv_number. Do i have to use set analysis? i want to have a multiple dimension for aggregation.

cv_number	territory_code	promo_amt	strProdcd	amount
5651	5101	31,196.91	56085	2,599.74
5651	5102	31,196.91	75005	2,599.74
5651	5103	31,196.91	56085	2,599.74
5651	5104	31,196.91	75005	2,599.74
5651	5105	31,196.91	56085	2,599.74
5651	5106	31,196.91	75003	2,599.74
5651	5107	31,196.91	56085	2,599.74
5651	5108	31,196.91	75005	2,599.74
5651	5109	31,196.91	56085	2,599.74
5651	5110	31,196.91	75005	2,599.74
5651	5111	31,196.91	56085	2,599.74
5651	5112	31,196.91	75005	2,599.77

Re: How to aggregate base on unique field

sunny_talwar — Mon, 17 Oct 2016 16:01:17 GMT

What would be the dimensions of your pivot table? Would you be able to provide us with the expected output (in a pivot table form) from your current sample?

Re: How to aggregate base on unique field

rotero — Tue, 18 Oct 2016 00:27:25 GMT

first i would like to have a dimension per document wherein i can display the promo amount per cv_number, then i will sum the promo amt per prod which is the amount field, i need to check if they have the same amount.

Eventually i will explore other dimension like per territory_code.

FYI originally as per checking the loaded data came from 2 diff table: its like header and details tables.

But initially i got the script the loaded the data as my example above.

Re: How to aggregate base on unique field

sunny_talwar — Tue, 18 Oct 2016 02:47:30 GMT

Ruby -

I am not sure I understand what you are looking to get. May be someone else can offer any advice

Best,

Sunny