Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Its not working my case I have different CODES values , table is still loaded with null values .