Use of Above or Previous

[alec1982]

hi all,

I have a table that looks like this

Item          Report Date     projected Date     Amount               Desired Column

Bread        09/07/2015      09/10/2015            500                         Null

Bread        09/07/2015     09/11/2015             200                         300

Bread        09/07/2015     09/12/2015             250                         250

Water        09/07/2015     09/10/2015             400                         Null

Water        09/07/2015     09/11/2015             220                         180

Bread        09/08/2015     09/10/2015             140                         Null

Bread        09/08/2015     09/11/2015              250                         -110

Need to create a column to calculate the first value of an item within the report date - current value specifically to each item and date as shown in the desired column..

any idea?

Best,

Alec

[sunny_talwar]

This?

Table:

LOAD * Inline [

Item,          Report Date,    projected Date,    Amount

Bread,        09/07/2015,      09/10/2015,            500

Bread,        09/07/2015,    09/11/2015,            200

Bread,        09/07/2015,    09/12/2015,            250

Water,        09/07/2015,    09/10/2015,            400

Water,        09/07/2015,    09/11/2015,            220

Bread,        09/08/2015,    09/10/2015,            140

Bread,        09/08/2015,    09/11/2015,              250

];

Table1:

LOAD *,

  If(Peek('Item') = Item and Peek('Report Date') = [Report Date], Alt(Peek('Amount'), Amount) - Amount) as DesiredColumn

Resident Table

Order By [Report Date], Item;

DROP Table Table;

Output:

[alec1982]

that is exactly what I asked for.. thank you but the request I have seems to be different.. they want to take the first value which is 500 for the first item and then subtract the next values from it so the desired column will need to be like this

Null

300

250

Null

-110

Null

180

[sunny_talwar]

Your initial post showed this:

I don't see how you came up with 250???

[alec1982]

500 the first value for the bread item - 250 the current value = 250

[alec1982]

your answer to the initial post is correct.. the end user is asking me to change it so we take the first value of an item and subtract the second value from it and then subtract the third value from it and so on...

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD * Inline [

Item,          Report Date,    projected Date,    Amount

Bread,        09/07/2015,      09/10/2015,            500

Bread,        09/07/2015,    09/11/2015,            200

Bread,        09/07/2015,    09/12/2015,            250

Water,        09/07/2015,    09/10/2015,            400

Water,        09/07/2015,    09/11/2015,            220

Bread,        09/08/2015,    09/10/2015,            140

Bread,        09/08/2015,    09/11/2015,              250

];

Join(Table)

LOAD Item,

  [Report Date],

  Min([projected Date]) as [projected Date],

  1 as Flag

Resident Table

Group By Item, [Report Date];

NewTable:

LOAD *,

  NewAmount - Amount as DesiredColumn;

LOAD *,

  If(Flag = 1, Amount, Peek('NewAmount')) as NewAmount

Resident Table;

DROP Table Table;

[alec1982]

this is almost working.. one issue, is on some rows it picks a different value on the new amount.. I think it is related to sorting the data but not sure how do I fix it.. I tried to do "order by" but didnt help....