Get previous row value when the value is null

[Anonymous]

Dear Experts,

I would like to copy the previous value when the current row value is null.

I tried the peek/previous functions but it is working only for the first blank row and it doesn't carry that logic to the next blank row.

I have Billing Lines and customers.For few billing lines I don't have the Customer details in which I want to copy the Customer number from the previous billing line.

Since the data is huge,could you please suggest the best way to achive this logic?

Thanks & Regards

Jeba

[Anonymous]

Hi Peter,

I tried with large data set.But it doesn't worked.

It is copying only one row and it doesn't go beyond that.i.e If I have 3 blank rows after 1st row its copying the first row value to next row only and not to the second rows and etc..

Is it because of the large data?

Any guidance will be a very big help for me

Regards

Jeba

[Peter_Cammaert]

No, the number of rows doesn't impact the correct execution of this LOAD statement. A regular LOAD statement executes row-by-row, and since you only look back one row (in the internal table), every evaluation will will cover a maximum of two rows at a time.

I think we can only help you further if you supply the actual script to us.

Peter

PS. Did you try the alternate solution suggested below by Thomas? It will only work when reading an external file, so you may have to temporarily store an internal table into a QVD.

[Anonymous]

Hi Peter,

Please find the code.

If(len(Trim(SHIPTO))=0,Peek(SHIPTO),SHIPTO) AS %SHIPTO_tmp

Resident VBRK_tmp

order by %BILLITEM

Thanks & Regards

Jeba

[Peter_Cammaert]

Simple solution:

• First order your table,
• only then and in a separate LOAD ... RESIDENT statement, use peek() to fill blank rows

ORDER BY and peek() don't work well together in the same LOAD...

Best,

Peter

[Anonymous]

Hi Peter,

I tried that as well but,its not working.

Please find the script for your kind reference.

Load *,
If(len(Trim(%SHIPTO_tmp))=0,Peek(%SHIPTO_tmp),%SHIPTO_tmp) AS %SHIPTO
FROM $(BUFFERDIRECTORY)VBRK_Temp.qvd(qvd);

Regards

Jeba

[thomaslg_wq]

Try this:

// I guess peek() needs -1 parameter :

//if order by needed:

TEMP:

Load *
FROM $(BUFFERDIRECTORY)VBRK_Temp.qvd(qvd)

// order by ORDERFIELD

;

REAL:

Load *,
If(len(Trim(%SHIPTO_tmp))=0,Peek(%SHIPTO_tmp,-1),%SHIPTO_tmp) AS %SHIPTO
resident TEMP;

Drop table TEMP;

//if order by not needed

REAL:

Load *,
If(len(Trim(%SHIPTO_tmp))=0,Peek(%SHIPTO_tmp,-1),%SHIPTO_tmp) AS %SHIPTO
resident TEMP;

[Anonymous]

Hi Thomas,

Could you please give me more information about this logic?

Regards

Jeba

[thomaslg_wq]

HI,

1 : in script windows, go on table file load,

2 : load your file, but don't apply now -> next,-> enable transformation step

3 : go in fill tab, and choose, -> fill -> target column 2 -> cell condition (is empty) // above

NEXT NEXT FINISH

It will fill empty rows with above value

[Peter_Cammaert]

Peek() accepts a field name (as a text string), not a field itself. You should use Peek('%SHIPTO_tmp').

Best,

Peter

[NareshGuntur]

This might help you

Re: Peek back till not Null

Cheers,

Naresh