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?
Solved! Go to Solution.
Load * inline
sum(Val) as Amount
Group by Type;
// drop table Tbl;
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?
This is a bit of a guess, but try:
LOAD * INLINE [
LOAD Field2, Sum(Field1) As Total RESIDENT Data
GROUP BY Field2;
LOAD Field2, Total,
If(Previous(Total) > 0, Previous(Total), Total) As TempTotal
If(Previous(Total) > 0, Previous(TempTotal) + Total, Total) As RunningTotal
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.
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?