Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
PLE
Contributor II
Contributor II

Complete rows with data

Dear all, can somebody help me with below.

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

InvoiceNrIDAccountAmountReference
2021033113001000 
202103311518-200 
202103318400-800WO20210817

 

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

 

Labels (1)
1 Solution

Accepted Solutions
paulselousyoriz
Partner
Partner

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

 

View solution in original post

3 Replies
paulselousyoriz
Partner
Partner

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

 

View solution in original post

sasikanth
Master
Master

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
Contributor II
Contributor II
Author

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

Regards,

Paul