Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
akmalquamri
Contributor III
Contributor III

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

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.

Labels (4)
2 Solutions

Accepted Solutions
daturpin
Partner - Creator II
Partner - Creator II

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;

View solution in original post

daturpin
Partner - Creator II
Partner - Creator II

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)
];

 

View solution in original post

2 Replies
daturpin
Partner - Creator II
Partner - Creator II

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
Partner - Creator II
Partner - Creator II

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)
];