topic Creating a new field from the Sum of an existing field in QlikView

Creating a new field from the Sum of an existing field

Fri, 22 Oct 2010 14:55:43 GMT

Hi. I'm new to QV and hope someone here can help me out 🙂

I have a table with two fields with this kind of structure:

Field1 - Field 2

10 - A
100 - A
1000 - A
20 - B
50 - C
500 - C

What I want to do is to take this information and create a new table with the sums of Field1 for represented for each Field2, like so:

1110 - A
20 - B
550 - C

I would like to do this in the script. What's the easiest way?

Thanks!

Creating a new field from the Sum of an existing field

Fri, 22 Oct 2010 15:02:10 GMT

Data:
LOAD * INLINE [
Field1,Field2
10,A
100,A
1000,A
20,B
50,C
500,C
];
LOAD Field2, Sum(Field1) As Total RESIDENT Data
GROUP BY Field2;

Creating a new field from the Sum of an existing field

Fri, 22 Oct 2010 15:04:18 GMT

try this:


Tbl:
Load * inline
[Val, Type
0, A
100, A
1000, A
20, B
50, C
500, C
];

sum:
Load Type,
 sum(Val) as Amount
Resident Tbl
Group by Type;

// drop table Tbl;

Creating a new field from the Sum of an existing field

Fri, 22 Oct 2010 15:08:29 GMT

Hi NMiller,

I am working hard, next time ....

Many Regards

Roland

Creating a new field from the Sum of an existing field

Fri, 22 Oct 2010 15:09:16 GMT

Thanks!

And if I want to load the data from an external QVD file? I tried FROM at the end but can't get it to work?

Creating a new field from the Sum of an existing field

Fri, 22 Oct 2010 15:12:11 GMT

Hi,

use the (file-) wizard to create your default load-statement and do the rest like grouping etc. manually.

Regards Roland

Creating a new field from the Sum of an existing field

Fri, 22 Oct 2010 15:25:00 GMT

Okay thanks! Got that to work.

But I have one further question, a bit trickier I assume

I want to that on every row the sum of that row AND all previous rows should be in the field.

Using the above example that would mean:

A - 1110

B - 1130

c - 1680

How do I accomplish this?

Creating a new field from the Sum of an existing field

Fri, 22 Oct 2010 15:43:05 GMT

Hi,

sorry, the weekend is waiting.

Creating a new field from the Sum of an existing field

Fri, 22 Oct 2010 15:45:22 GMT

This is a bit of a guess, but try:

Data:
LOAD * INLINE [
Field1,Field2
10,A
100,A
1000,A
20,B
50,C
500,C
];
Data2:
LOAD Field2, Sum(Field1) As Total RESIDENT Data
GROUP BY Field2;
Data3:
LOAD Field2, Total,
If(Previous(Total) > 0, Previous(Total), Total) As TempTotal
RESIDENT Data2;
Data4:
LOAD Field2,
If(Previous(Total) > 0, Previous(TempTotal) + Total, Total) As RunningTotal
RESIDENT Data3;

I tested it in a sample app, but I admit it is pretty ugly. There's probably a way to clean it up a bit.

Creating a new field from the Sum of an existing field

Sat, 23 Oct 2010 13:09:46 GMT

Okay yeah I understand how this works thanks! , but the actual table I'm using have thousands of rows Maybe some sort of looping mechanism would do it? How can I accoplish this?

Creating a new field from the Sum of an existing field

Sat, 23 Oct 2010 13:22:21 GMT

...to clarify somewhat: I have a table with two fields one date field and one balance field - there can be more than 1 rows per date with different balances for each row.

I want to create a new table where a row equals = the date field + the sum of all balance field for that date PLUS the sum of the balance fields for all previous dates.

Creating a new field from the Sum of an existing field

Sat, 23 Oct 2010 22:08:33 GMT

Hi mauich123,

I think you should open a new thread with your new question(s). This thread has the green checkbox for solved threads, not much people will therefor watch it.

Regards

Roland