Running count (title for 3 continuous months)

[yipchunyu]

Like normal company, our staffs will be promoted (or demoted) from time to time.  Any way to get the latest title which has been effective for more than 3 months?

Name	Jan	Feb	Mar	Apr	May
Staff 1	AM	AM	AM	AM	AM
Staff 2	RM	RM	RM	SRM	SRM
Staff 3	BM	BM	SBM	SBM	SBM
Staff 4	AM	M	M	M	M
Staff 5	RM	RM	RM	SRM	RM

For example, assuming it's May now
Staff 1 should be AM
Staff 2 still be RM (since he only be SRM for 2 months)
Staff 3 will counted as SBM (as his title has been effective for 3 months)
Staff 4 will counted as M (as his title has been effective for more than 3 months as well)
Staff 5 will counted as RM (as latest 3 continuous month's title is RM)
The requirements are a little bit wired but it's what I need to handle.  Pls share your ideas.  Thx in advance.

[anushree1]

Use the below Script:

Temp:
CrossTable(Month,Designation)
LOAD
Name,
Jan,
Feb,
Mar,
Apr,
May
FROM<<Table Name along with path>>;

Table1:
Load * ,
if(peek(Name,-2)=Name,'Apt','NA'),
if(peek(Name,-2)=Name and peek(Designation,-2)= Designation,peek(Designation,-2)) as CurrentDesignation;
Load *
Resident Temp
order by Name;
Drop Table Temp;

Create a table with Name And Designation in the front end to get the desired result