Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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');
Hi Paul, thanks for the info. It works perfectly with the mapping.
Regards,
Paul