How to get previous rows last column values in current row first column in Qlik sense flat table?

[akmalquamri]

Date	Col_Val1	Col_Val2	Col_Val3	Col_Val4
1-sept	21	0	2	19
2-sept	19	3	8	14
3-sept	14	6	1	19

Here Col_Val1 is dependent on the previous day's last col value, and last column values = Col_Val1+Col_Val2-Col_Val3. I want to do this at the front end of the table chart.

[daturpin · Accepted solution]

You'll have to build it on the fly. I'm not sure where your data is stored but let me assume it's a QVD:

[raw_table]:

REPLACE LOAD

Date,

Col_Val2,

Col_Val3

From  "$(your_path)/your_file.qvd" (qvd, utf8)

Order by Date;

LET NumRows=NoOfRows('raw_table');

If $(NumRows) > 0 then

    LET vCol_Val1 = $(start_value)

    FOR i = 0 to $(NumRows)-1

        LET vDate = peek('Date', $(i), 'raw_table'); 

        LET vCol_Val2 = peek('Col_Val2', $(i), 'raw_table'); 

        LET vCol_Val3 = peek('Col_Val3', $(i), 'raw_table'); 

        LET vCol_Val4 = $(vCol_Val1) + $(vCol_Val2) - $(vCol_Val3);

        [finished_table]:

        ADD LOAD 

        *

        INLINE [

        'Date','Col_Val1','Col_Val2','Col_Val3', 'Col_Val4)

        $(vDate), $(vCol_Val1), $(vCol_Val2), $(vCol_Val3), $(vCol_Val4)

        ];

        LET vCol_Val1 = $(vCol_Val4);

    NEXT i;

End if;

[daturpin · Accepted solution]

Once you get that working, you'll need to decide when you want it to reload. As is, it will work on both a partial and full load, and it never deletes the finished_table.

You can put a drop table command in there on reload, or reset it with an "empty" inline load:

[finished_table]:

REPLACE LOAD 

*

INLINE [

'Date','Col_Val1','Col_Val2','Col_Val3', 'Col_Val4)

];