Holding Previous Key Data Value in Stage Variable

[dhiren]

Hi,
Here are my input rows sorted on two columns REC_72_NR (in ascending order ) and MOVE_DT(in descending order):

REC_72_NR MOVE_DT
50 20010808
50 20010807

Now i wish to have the following column END_DT in the output.
The End_Dt of the last record (the highest Move_Dt) for the same REC_72_NR must be defaulted to 20501231

REC_72_NR MOVE_DT END_DT
50 20010808 20501231
50 20010807 20010808

To achieve this , i have used a Sort Stage before Transformer stage and have produced a Key change column in the sort stage.

I have used two stage Variables in the Xmer :
StgCurrentValue and StgPrevValue

The derivation of stage variables i have written is as below
1.) StgPrevValue = StgCurrentValue
2.) StgCurrentValue = MOVE_DT

In the Xmer, i have the following expression for End_Dt
If Key_Change = 1 then End_Dt = 20501231
Else End_Dt = StgPrevValue.

What i see in the file is StgPrevValue and StgCurrentValue are not reflecting the correct values as it should have been.
The data value for previous row is not seen. i.e.
The stage variable for the first row is not showing its initialised Value.

I understand the order of execution of Stage Variable here plays an important role. How do i give the order for execution for these Stage Variables? Any snytax help or other alternative for achieving this output will be of great help.

Thanks in advance
Regards,
Dhiren

[ArndW]

dhiren,

stage variables are executed in the order in which they appear in the list. I think in your case you don't want to store the previous row's values, but the lowest date value (or the 1st date value after a group change)

So you could change your transform to store the REC_72_NR column and see if it has changed, if so then store the MOVE_DT. I don't see why you are using a fixed date value in your example instead of the actual column value - is this first always fixed?

[dhiren]

Hi,
I think i missed my following requirement.

For a Rec_72_Nr, the highest Move_Dt will have the default value for End_Dt, but the other End_Dt will have the next value of Move_Dt.

Lets say there are 3 record in input

Rec_72_Nr Move_Dt
50 20010809.
50 20010808
50 20010807

The Output will look like this:

Rec_72_Nr Move_Dt End_Dt
50 20010809 20501231
50 20010808 20010809
50 20010807 20010808

Regards,
Dhiren

[ArndW]

dhiren,

so your rule is "sort the incoming data by rec_72_Nr and Move_Dt and use the previous Move_Dt unless it is a new Rec_72_Nr in which case use a fixed date"

You will need to remember both the last Rec_72_Nr and Move_Dt in stage variables, but your approach listed in the original question will work.

[Sunshine2323]

Hi Dhiren,

I have achieved your requirement using 2 jobs. You can use the Basic Transformer to use the RowProc Transforms.

I had taken Input as

51,20010809
50,20010808
50,20010807
50,20010806
50,20010805
49,20010806
49,20010805
47,20010806
47,20010805


First Job: I am just achieving the first condition that the highest Move_Dt will have the default value for End_Dt

Input File--->Transformer---> OutputFile1

Transformer Derivation:

Defining a Stage Variable for finding Key Change and inserting default value.

Stage Variable

StgENDDT - if (RowProcCompareWithPreviousValue(DSLink4.Key)=1) then '' else '20501231'

So now the output file will have '20501231' for END_DT where Move_Dt is higest rest all the values will be null.

Column Derivation:

Rec_72_Nr---> Input File. Rec_72_Nr
Move_Dt---> Input File. Move_Dt
END_DT---> StgENDDT

Output after first job:

51,20010809 ,20501231
50,20010808 ,20501231
50,20010807,
50,20010806,
50,20010805,
49,20010806,20501231
49,20010805,
47,20010806,20501231
47,20010805,



Second Job: Filling in the Null Values

OutputFile1--->Transformer---> OutputFile

Stage Variable

StageVar- OutputFile1.Value

StageVar1- If IsNull(DSLink7.END_DT) then RowProcGetPreviousValue(StageVar) else ''

StageVar2- RowProcGetPreviousValue(StageVar)


Column Derivation:

Rec_72_Nr: OutputFile1. Rec_72_Nr
Move_Dt: OutputFile1. Move_Dt
END_DT: if Not(IsNull(DSLink7.END_DT)) then DSLink7.END_DT else StageVar1


and got the below output

51,20010809 ,20501231
50,20010808 ,20501231
50,20010807,20010808
50,20010806,20010807
50,20010805,20010806
49,20010806,20501231
49,20010805,20010806
47,20010806,20501231
47,20010805,20010806


Hope this was your requirement.