Scripting SUM on value of field2 when field1 has the same value.
Hi community,
I am completely new using Qlik and dying with the Task given to me. Basically I am given a table of two-columns.
Column1
column2
1
-32
1
30
1
2
2
33
2
22
1. I would like to create another table which sums the value of column2 when column1 has the same value . The result would be something like this :
1
0
2
55
I am not sure whether I going to intrepret this right :
but I would like to implement a script without having to establish the value from column1. In a way I would like to script in a way that If there are other values (e.g : 3) in column1, then it would add it to the result table.
This is a clear example of "Group by" use. Try this script in a QV file:
SourceTable:
Load * Inline [
Column1, Column2
1, 2
1, 3
2, 2
2, 5
2, -7
3, 9
];
ModifiedTable:
NoConcatenate Load
Column1,
Sum(Column2) as SumedColumn2
resident SourceTable
group by Column1;
Drop table SourceTable;
I load a source table as your two column table. Then, I create a new transformed table from "SourceTable". This transformation does what you need thanks to "group by Column1". This clause does exactly what it sais: groups by one or more fields of your table so you can aggregate (sum, avg, max, min...) the other ones. You should group by all filds that are NOT going to be aggregated (you'll get an error if not).
