Set field during load to value in previous row

[#1]

Hi All

I have a raw data file that I am loading that has different line types.  One is the Total and another is the associated detail.  However the Detail lines do not have a reference on them, that is held on the Total Line.

I am looking to during the load if it is a Total Line set a field to the reference and then if it is a detail line set the field to the reference on the previous load line.  This accounts for variable number of detail lines as when the next Total is reached it resets the reference.

I have been able to do part of it using .....

if(@1=2,@15,if(@1=3,RowNo()-1)) as ref_field

This sets the field for the Total line, and then if detail line gets the previous rowno().  How do I get it to populate the field with the value of ref_field from the previous rowno and not just the previous rowno.

Many thanks

Mark

[#2 · Accepted solution]

Mark,

I'm making the assumption that @1 = 2 identifies a Total line and = 3 identifies a detail?

Try this in the script:

if (@1= 2, @15, peek('@15')) as ref_field,

Regards,

Gordon

[#3]

Hi Gordon,

Yes @1=2 is Total and 3 is detail.  The script nearly worked, only actioned for one additional line.

Below is a snapshot of the data. 

RowNo | Type | Batch Ref | Ref Field | peek

You can see in the ref field i have got it to change on every Type 2, and then show the previosu row number.  The peek does bring in value from previous row for @15 but it has different context on a total to detail line.  what i was looking was for it to repeat the ref field which is why i was trying to create a new ref field

[#4]

Hi Gordon

Got it, cheers

if(@1=2,@15,peek('ref_Field)) as ref_field

Cheers