DSXchange

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

Stage variables can do that. What did you try?

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

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

You need to execute this in sequential mode.

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?

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

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...

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.

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

See above.

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.

Yeah, well you made me feel guilty...

Eric,

Thank you very much for providing the solution.

Thanks
Vineet

You're welcome; glad I could help.