Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
kindly below my input and require output
Table Input
Location | Item | LineID | Days | Req | Available |
XXX | ABC | Line1 | 46 | 1 | 32 |
XXX | ABC | Line2 | 40 | 1 | 32 |
YYY | ABC | Line3 | 55 | 1 | 6 |
YYY | ABC | Line4 | 43 | 1 | 6 |
YYY | ABC | Line5 | 40 | 1 | 6 |
YYY | ABC | Line6 | 34 | 1 | 6 |
YYY | ABC | Line7 | 23 | 1 | 6 |
YYY | ABC | Line8 | 5 | 1 | 6 |
YYY | ABC | Line9 | 3 | 1 | 6 |
Output shoul be like than:
Location | Item | LineID | Days | Req | Available | output |
XXX | ABC | Line1 | 46 | 1 | 32 | 31 |
XXX | ABC | Line2 | 40 | 1 | 32 | 30 |
YYY | ABC | Line3 | 55 | 1 | 6 | 5 |
YYY | ABC | Line4 | 43 | 1 | 6 | 4 |
YYY | ABC | Line5 | 40 | 1 | 6 | 3 |
YYY | ABC | Line6 | 34 | 1 | 6 | 2 |
YYY | ABC | Line7 | 23 | 1 | 6 | 1 |
YYY | ABC | Line8 | 5 | 1 | 6 | 0 |
YYY | ABC | Line9 | 3 | 1 | 6 | -1 |
How is output calculated? Please share some insight, we cannot read minds I am afraid...
Maybe this...
LOAD
Location,
Item,
LineID,
Days,
Req,
Available,
if (isnull(peek('Location', -1)) or peek('Location', -1) <> Location or peek('Item', -1) <> Item,
Available - Req,
peek('output', -1) - Req) as output
RESIDENT [Your Table]
Order by Location, Item, LineID