Aggregation

[ziadm]

Please let me know why this does not work

Table1:

LOAD * INLINE [

    Code, Name, Value

    1, TEST1, 1

    1, Test2, 2

    3, Test, 4

    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

[swuehl · Accepted solution]

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;

or

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?

[ziadm]

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

[swuehl]

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.

[maxgro]

for this the script is

Table2:

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

Resident Table1

group by Code;