Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Maas
Contributor
Contributor

Field Forward filled over a period

Hello Dears

I have below table and I need to forward fill the gaps between date periods with the latest rate for this period in load script.

ROWNUM DESC Code Date RATE
27 Debit Interest Rate-Loan 774 11 04/03/2020 2.5
0 - - 05/03/2020 -
0 - - 06/03/2020 -
0 - - 07/03/2020 -
0 - - 08/03/2020 -
0 - - 09/03/2020 -
0 - - 10/03/2020 -
0 - - 11/03/2020 -
0 - - 12/03/2020 -
0 - - 13/03/2020 -
0 - - 14/03/2020 -
0 - - 15/03/2020 -
26 Debit Interest Rate-Loan 774 11 16/03/2020 1.5
0 - - 17/03/2020 -
0 - - 18/03/2020 -
0 - - 19/03/2020 -
0 - - 20/03/2020 -
0 - - 21/03/2020 -
0 - - 22/03/2020 -
0 - - 23/03/2020 -
0 - - 24/03/2020 -
0 - - 25/03/2020 -
0 - - 26/03/2020 -
0 - - 27/03/2020 -
0 - - 28/03/2020 -

what I want to achieve :

ROWNUM DESC Code Date RATE
27 Debit Interest Rate-Loan 774 11 04/03/2020 2.5
0 Debit Interest Rate-Loan 774 11 05/03/2020 2.5
0 Debit Interest Rate-Loan 774 11 06/03/2020 2.5
0 Debit Interest Rate-Loan 774 11 07/03/2020 2.5
0 Debit Interest Rate-Loan 774 11 08/03/2020 2.5
0 Debit Interest Rate-Loan 774 11 09/03/2020 2.5
0 Debit Interest Rate-Loan 774 11 10/03/2020 2.5
0 Debit Interest Rate-Loan 774 11 11/03/2020 2.5
0 Debit Interest Rate-Loan 774 11 12/03/2020 2.5
0 Debit Interest Rate-Loan 774 11 13/03/2020 2.5
0 Debit Interest Rate-Loan 774 11 14/03/2020 2.5
0 Debit Interest Rate-Loan 774 11 15/03/2020 2.5
26 Debit Interest Rate-Loan 774 11 16/03/2020 1.5
0 Debit Interest Rate-Loan 774 11 17/03/2020 1.5
0 Debit Interest Rate-Loan 774 11 18/03/2020 1.5
0 Debit Interest Rate-Loan 774 11 19/03/2020 1.5
0 Debit Interest Rate-Loan 774 11 20/03/2020 1.5
0 Debit Interest Rate-Loan 774 11 21/03/2020 1.5
0 Debit Interest Rate-Loan 774 11 22/03/2020 1.5
0 Debit Interest Rate-Loan 774 11 23/03/2020 1.5
0 Debit Interest Rate-Loan 774 11 24/03/2020 1.5
0 Debit Interest Rate-Loan 774 11 25/03/2020 1.5
0 Debit Interest Rate-Loan 774 11 26/03/2020 1.5
0 Debit Interest Rate-Loan 774 11 27/03/2020 1.5
0 Debit Interest Rate-Loan 774 11 28/03/2020 1.5

I have Generated these periods between dates by SQL as my bas table only contains specific dates, now on Qlik iam trying to fill all the null values with rates from pervious not null value.

Labels (4)
2 Replies
aguirre
Creator
Creator

You can use Peek function.

Ex.

tabx:

LOAD * INLINE [
ROWNUM,DESC,CODE,Date
27,Debit Interest,11,04/03/2020
0,,,06/03/2020
0,,,07/03/2020
0,,,08/03/2020
26,Debit Interest,11,04/03/2020
0,,,9/03/2020
0,,,10/03/2020
];

tab1:
noConcatenate
LOAD ROWNUM, IF (ROWNUM=0, Peek([DESC]), [DESC]) as DESC,IF (ROWNUM=0, Peek([CODE]), [CODE]) as CODE
Resident tabx;

drop table tabx;

Maas
Contributor
Contributor
Author

Its not working my case I have different CODES values , table is still loaded with null values .