Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Here is my script and qvw file attached
TEMP:
LOAD * INLINE [
Vendor, Date, Value
1, 1/1/2016, 100
1, 2/1/2016, 200
2, 3/1/2016, 300
2, 4/1/2016, 400
3, 5/1/2016, 200
4, 6/1/2016, 100
4, 7/1/2016, 100
5, 8/1/2016, 90
];
Data:
LOAD*,Peek('Value') as NextMonthValue
Resident TEMP
Order By Vendor, Date desc;
Drop Table TEMP;
Here is the result
Vendor Date Value NextMonthValue
1 1/1/2016 100 200
1 2/1/2016 200 -
2 3/1/2016 300 400
2 4/1/2016 400 100
3 5/1/2016 200 300
4 6/1/2016 100 500
4 7/1/2016 500 200
5 8/1/2016 90 100
Here is what I want. Grab next months value. If Vendor has no value for next month, null. Need to do this in the script, not expression
Vendor Date Value NextMonthValue
1 1/1/2016 100 200
1 2/1/2016 200 -
2 3/1/2016 300 400
2 4/1/2016 400 -
3 5/1/2016 200 -
4 6/1/2016 100 100
4 7/1/2016 500 -
5 8/1/2016 90 -
Try this:
TEMP:
LOAD * INLINE [
Vendor, Date, Value
1, 1/1/2016, 100
1, 2/1/2016, 200
2, 3/1/2016, 300
2, 4/1/2016, 400
3, 5/1/2016, 200
4, 6/1/2016, 100
4, 7/1/2016, 500
5, 8/1/2016, 90
];
Data:
LOAD *,
If(Vendor = Previous(Vendor), Peek('Value')) as NextMonthValue
Resident TEMP
Order By Vendor, Date desc;
Drop Table TEMP;
Try this:
TEMP:
LOAD * INLINE [
Vendor, Date, Value
1, 1/1/2016, 100
1, 2/1/2016, 200
2, 3/1/2016, 300
2, 4/1/2016, 400
3, 5/1/2016, 200
4, 6/1/2016, 100
4, 7/1/2016, 500
5, 8/1/2016, 90
];
Data:
LOAD *,
If(Vendor = Previous(Vendor), Peek('Value')) as NextMonthValue
Resident TEMP
Order By Vendor, Date desc;
Drop Table TEMP;
Thank you Sunny!
If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post).
If not, please make clear what part of this topic you still need help with .
Onno,
I do not see a "Correct Answer" button near any posts. I've seen this button before in the past but do not see it anywhere now. There's an "Actions" drop down menu with "Marked as Helpful". That's the only action available located on every post.
Sunny did answer my question.
See if this link helps