Need Help to Implement Logic

Vineet Pal Singh · Post by **Vineet Pal Singh** » Tue Apr 24, 2012 6:56 pm

Hi,

I am trying to implement the following scenario which came up as part of our recent requirements. I have stated the logic below.

We have a Product table which has with Product and Amount as it's attributes.

Product ID Amount
FX123 100
Ab234 200
XZ567 300
SD234 400

As per the logic we have to come up with a third column Adjusted Amount while loading the database which is as follows

Product ID Amount AdjAmt
FX123 100 Null
Ab234 200 100
XZ567 300 200
SD234 400 300

So we have to populate the first record AdjAmt as 'Null' and from second record onwards we have to take the amount from first record and so on.

I tried implementing with the help of stage variables but it didnot help.
Would appreciate if someone can provide some insight on the above logic.

Thanks
Vineet Singh

qt_ky · Post by **qt_ky** » Tue Apr 24, 2012 7:32 pm

Stage variables can do that. What did you try?

Vineet Pal Singh · Post by **Vineet Pal Singh** » Tue Apr 24, 2012 7:37 pm

How to identify the first record where the amount has to be populated as 'Null'.

kwwilliams · Post by **kwwilliams** » Tue Apr 24, 2012 9:10 pm

Sort by amount ascending. The first record would be null.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 25, 2012 2:49 am

You need to execute this in sequential mode.

Vineet Pal Singh · Post by **Vineet Pal Singh** » Wed Apr 25, 2012 11:45 am

Thanks Ray.

So I know how to get the first and populate 'Null' in it.
How should I really go about populating the second record with the amount from first record and so on?

us1aslam1us · Post by **us1aslam1us** » Wed Apr 25, 2012 11:56 am

Vineet,

It can all be done by using a stage variable to store the previous value and populate this vale in the current Column.

But why would anyone like to modify the Amounts associated with Products in such a fashion unless this is some kinda fix.

sAM

chulett · Post by **chulett** » Wed Apr 25, 2012 12:30 pm

us1aslam1us wrote:It can all be done by using a stage variable to store the previous value and populate this vale in the current Column.

Just wanted to point out that simply saying you can do it doesn't help someone who doesn't know how to do it. Best to flesh out an answer like this with an actual example of how one would make that work...

qt_ky · Post by **qt_ky** » Wed Apr 25, 2012 3:55 pm

Right click, stage variable properties, it will open a metadata table. In there you can also choose to set initial values.

Stage variables are executed once per row in top to bottom order. Here is some pseudo-code example of one way (run this in sequential mode after you have sorted according to your needs):

svAdjAmt set to: If @INROWNUM = 1 Then SetNull() Else svPreviousAdjAmt
svPreviousAdjAmt set to: lnk.AdjAmt

Depending on your version you may not be able to set a stage var to null; you could set it to 999999 or something else instead. Then drag the svAdjAmt down to your new AdjAmt column on the output link.

Vineet Pal Singh · Post by **Vineet Pal Singh** » Wed Apr 25, 2012 3:56 pm

Sam,
The requirements are such that we have to implement this logic.I am not driving the requirements, just implementing them.

Craig,
Could you please provide some pointers on how to do it?

Thanks
Vineet Singh

qt_ky · Post by **qt_ky** » Wed Apr 25, 2012 3:57 pm

See above.

chulett · Post by **chulett** » Wed Apr 25, 2012 4:24 pm

Vineet Pal Singh wrote:Could you please provide some pointers on how to do it?

I actually left that as an exercise for us1aslam1us to complete, but it seems Eric beat him to it.

qt_ky · Post by **qt_ky** » Wed Apr 25, 2012 4:27 pm

Yeah, well you made me feel guilty...

Vineet Pal Singh · Post by **Vineet Pal Singh** » Wed Apr 25, 2012 4:36 pm

Eric,

Thank you very much for providing the solution.

Thanks
Vineet

qt_ky · Post by **qt_ky** » Wed Apr 25, 2012 5:04 pm

You're welcome; glad I could help.