Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Last 5 rows of data

Hi,

I have a back end table that looks something like the file attached.

For each row and each Key I want to be able to look at previous 4 rows and count the number of Advisor lines and calculate the field 'Last5Count' as in the excel attached, could someone please suggest a solution.

Please note i have many more fields and have 800k rows worth of data.

Regards

Rahul

1 Solution

Accepted Solutions
tresesco
Not applicable

Re: Last 5 rows of data

PFA

Load
*,
SubStringCount(Peek(Identifer_by)&Peek(Identifer_by,-2)&Peek(Identifer_by,-3)&Peek(Identifer_by,-4)&Peek(Identifer_by,-5),'Advisor') as Last5Count;

LOAD Key,
    LineCount,
    Post_Diff_NonSystem,
    Identifer_by,
    Advisor_Counter,
    System_Counter,
    Customer_Counter,
    NonSystem_Counter
    // Last5Count
FROM

(ooxml, embedded labels, table is Sheet1);

1 Reply
tresesco
Not applicable

Re: Last 5 rows of data

PFA

Load
*,
SubStringCount(Peek(Identifer_by)&Peek(Identifer_by,-2)&Peek(Identifer_by,-3)&Peek(Identifer_by,-4)&Peek(Identifer_by,-5),'Advisor') as Last5Count;

LOAD Key,
    LineCount,
    Post_Diff_NonSystem,
    Identifer_by,
    Advisor_Counter,
    System_Counter,
    Customer_Counter,
    NonSystem_Counter
    // Last5Count
FROM

(ooxml, embedded labels, table is Sheet1);