topic Aggregation in QlikView

Aggregation

ziadm — Fri, 06 Nov 2015 17:25:52 GMT

Please let me know why this does not work

Table1:

LOAD * INLINE [

Code, Name, Value

1, TEST1, 1

1, Test2, 2

3, Test, 4

4, Test, 4

5, Test11, 4

5, Test12, 6

];

NoConcatenate

Table2:

load

Code,

Only(Name),

Sum(Value) as Total

Resident Table1

Group by Code,Name;

I need to aggregate by Code, Name (Only one value) and Sum of Value

Code	Only(Name)	Total
1	TEST1	1
1	Test2	2
3	Test	8
4	Test	4
5	Test11	4
5	Test12	6

Thanks

Re: Aggregation

swuehl — Fri, 06 Nov 2015 17:29:20 GMT

If there are more multiple Names per Code, which one to return?

I think you want to group by Code only, not also per Name:

load

Code,

FirstValue(Name) as Name,

Sum(Value) as Total

Resident Table1

Group by Code;

load

Code,

Concat(Name, ', ') as Name,

Sum(Value) as Total

Resident Table1

Group by Code;

edit:

Or maybe I misunderstand what you are trying to achieve. Is the last table the expected or actual result? If it's the expected result, what do you get with the current script?

Re: Aggregation

ziadm — Fri, 06 Nov 2015 17:58:28 GMT

Thanks

I need to display the name using any aggregation functions mode, max string. ..etc.

I still have to group by name returning one value is OK

Re: Aggregation

swuehl — Fri, 06 Nov 2015 18:14:13 GMT

Sorry, I am still missing your point.

Can you detail based on above input table, how the output table should look like?

Your script returns the second table, so I assume that's not what you want.

Re: Aggregation

maxgro — Fri, 06 Nov 2015 18:58:15 GMT

for this the script is

Table2:

load Code, MaxString(Name), sum(Value)

Resident Table1

group by Code;