How to transform a table

[#1]

Dear all,

I'm newer and I'm facing the the following problem. I've a table like this:

Supplier     Year      Month     Paid amount

A               2012     01          1000

A               2012     02          1500

A               2012     03          800

B               2012     01          5000    

B               2012     02          2000

B               2012     03          1500

B               2012     04          1000

where each line, represents the paid amount in the month (per year and supplier)

I've to build a new table like this:

Supplier     Year     Month     Total paid amount at the month

A               2012     01          1000

A               2012     02          2500 (1000 + 1500)

A               2012     03          3300 (1000 + 1500 + 800)

B               2012     01          5000

B               2012     02          7000 (5000 + 2000)

B               2012     03          8500 (5000 + 2000 + 1500)

B               2012     04          9500 (5000 + 2000 + 1500 + 1000)

where each line, contains the progressive paid amount. For example: for the supplier A, the second line contains the sum of  January (01) and February(02),

1000 + 1500.

The third line, is the contains the sum of 1000 + 1500 + 800 and so on

I hope somebody can help me

Thank in advance

[swuehl · Accepted solution]

Hi peronist,

you should ensure that your table is ordered appropriately, then you can use peek() function to retrieve the value of a field in a previous record (i.e. this allows you to "add up" values from to different record lines):

INPUT:

LOAD * INLINE [

Supplier,     Year,      Month,     Paid amount

A,               2012,     01,          1000

A,               2012,     02,          1500

A,               2012,     03,          800

B,               2012,     01,          5000   

B,               2012,     02,          2000

B,               2012,     03,          1500

B,               2012,     04,          1000

] ;

FACT:

LOAD *,

rangesum([Paid amount],if(Supplier=peek(Supplier),peek(Total))) as Total

Resident INPUT order by Supplier, Month;

drop table INPUT;

If you have multiple years, you may want to order by Supplier, Year, Month and also include a check for Year in the if() statement:

rangesum([Paid amount],if(Supplier=peek(Supplier) and Year=peek(Year),peek(Total))) as Total

Hope this helps,

Stefan

[#3]

Really Thanks !!! It works

Stefan