Creating a new field in the script using massive calculations from several fields

[alec1982]

Hi,

I have the following table that shows a schedule of payements

ID     Payement Date     Amount     Most Recent payement

1     3/1/2012                  800           1000

1     5/1/2012                  1000         1000  

1     6/1/2012                  1000         1000

1     10/1/2012                 1000        1000  

2     1/1/2010                   300           500

2     6/1/2012                   500           500

2     8/1/2012                    500          500

3     4/1/2011                    300         1000

3     5/1/2012                   1000        1000

3     12/1/2012                  1000       1000

I need to calculate a new field to show the most recent payement.(The red field ).

the calculation should look at today's date and compare it to the the date field(Month/Year) and then put the amount of that row in a new field next to the ID.

I Am not sure how to do it..and i have' been thinking about it all day.

I appreciate any help..

Thxs,

[alec1982]

I am getting an error "field not found"

not sure what's causing the error

but on the main load i have:

load

ID as IHS ID

Payment Date AS Due Date

Amount As Due Amount

  and i have tried to do it the same as you sent me.

Thxs,

[Jason_Michaelides · Accepted solution]

temp:

LOAD ID,

     [Payment Date],

     Amount

FROM....;

Data:

LOAD

          ID

          ,LastValue([Payment Date])

          ,LastValue(Amount)

RESIDENT temp

WHERE [Payment Date] <= Today()

GROUP BY ID ORDER BY [Payment Date];

DROP TABLE temp;

See attached.

[alec1982]

Thxs man.

it worked perfectly.

[alec1982]

Hi again,

I still have an issue and wish you can help me with.

on the script that you sent me I am trying to add more fields and i am getting an error...

I am trying to add a calculated field of (AnnualAccurancy) *(Most recent Amount)..

the first field (Annual Accurancy ) is available in the first table and the second one is the lastvalue(MostRecentAmount) from the second table..

I appreciate your help.

Thxs,

[Jason_Michaelides]

Then you most likely need to join the tables together first. This is probably best as a new post.