Last 5 rows of data

[#1]

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

[tresesco · Accepted solution]

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