Load 3 years data per sales person

[ericdelaqua]

Hello,

I have a requirement that load only the latest  3 yeas data per Salesperson from source. I am looking for the easiest way to achieve this.

Sourcedata:

Load * Inline [SalesPerson,Year,Sales

Eric,2012,5000

Eric,2014,7000

Eric,2019,8000

Eric,2021,5000

frank,2014,12000

frank,2012,6500

Natalie,2019,2000

Natalie,2020,2500

Natalie,2021,3500

];

Expected Output

Eric,2014,7000

Eric,2019,8000

Eric,2021,5000

frank,2014,12000

frank,2012,6500

Natalie,2019,2000

Natalie,2020,2500

Natalie,2021,3500

Thanks 

[Vegar · Accepted solution]

Hi @ericdelaqua 

You can solve this using intervalmatch() and the two parameter Max() function.

Data:
Load * 
Inline [
	SalesPerson,Year,Sales
	Eric,2012,5000
	Eric,2014,7000
	Eric,2019,8000
	Eric,2021,5000
	frank,2014,12000
	frank,2012,6500
	Natalie,2019,2000
	Natalie,2020,2500
	Natalie,2021,3500
	];

inner join (Data)
IntervalMatch (Year,SalesPerson)
Load 
	alt(Max(Year,3),min(Year)) as Min,
	Max(Year,1) as Max,
	SalesPerson
Resident Data
group by SalesPerson

;
Drop field Min, Max;

- Vegar

[sasikanth]

HI, 

Try the below one,

Test:
Load * Inline [SalesPerson,Year,Sales

Eric,2012,5000

Eric,2014,7000

Eric,2019,8000

Eric,2021,5000

frank,2014,12000

frank,2012,6500

Natalie,2019,2000

Natalie,2020,2500

Natalie,2021,3500

];

TEMP:
load SalesPerson,Year,Sales,
if(Peek(SalesPerson)=SalesPerson,Peek(row)+1,1) as row
Resident Test order by SalesPerson, Year desc;

FINAL:
Load SalesPerson ,Year,Sales Resident TEMP where row<=3;

Drop Table Test,TEMP;