Complete rows with data

[PLE]

Dear all, can somebody help me with below.

I have below table coming from a singel table from our accounting database. 

InvoiceNrID	Account	Amount	Reference
20210331	1300	1000
20210331	1518	-200
20210331	8400	-800	WO20210817

The goal is to have the reference number completed within all the rows that are linked with the unique ID which is invoice number ID. Does anybody have an idea how to do this ?

Thanks.

Paul

[paulselousyoriz · Accepted solution]

Hi Paul,

There are two approaches to this.

1) Sort the table on InvoiceNrID and then on Reference - the last being DESC. Then you could use a PREVIOUS of PEEK to get the first value of the record with the same  InvoiceNrID value and get the corresponding Reference value. But PREVIOUS and PEEK.

2) Use a mapping load of InvoiceNrID and Reference only for records where Reference is not empty. Use this with an ApplyMap to populate the Reference field for all records.

I think option 2) is preferable as PREVIOUS/PEEK can cost more performance whilst the use of mapping/applymap is generally very performance efficient. Here is my code for option 2):

"Source:
Load * inline
[InvoiceNrID, Account, Amount, Reference
20210331,1300,1000,''
20210331,1518,-200,''
20210331,8400,-800,WO20210817
];

Reference_Mapping:
Mapping Load
InvoiceNrID,
Reference
Resident Source
Where Len(Reference) > 0;

Result:
NoConcatenate Load
InvoiceNrID,
Account,
Amount,
ApplyMap('Reference_Mapping',InvoiceNrID,'Error') As Reference
Resident Source;

drop table Source;"

Regards,

Paul

[sasikanth]

Can try below one, 

TABLE:
Load InvoiceNrID,
Account,
Amount,
if(isnull(Peek(InvoiceNrID))
OR ( InvoiceNrID=Peek(InvoiceNrID) and len( Reference)=0),InvoiceNrID,Reference) as Reference;
load * Inline[
InvoiceNrID Account Amount Reference
20210331 1300 1000
20210331 1518 -200
20210331 8400 -800 WO20210817

](delimiter is '\t');

[PLE]

Hi Paul, thanks for the info. It works perfectly with the mapping. 

Regards,

Paul