topic Load 3 years data per sales person in App Development

Load 3 years data per sales person

ericdelaqua — Tue, 30 Nov 2021 18:17:33 GMT

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

Re: Load 3 years data per sales person

Vegar — Tue, 15 Jun 2021 06:31:24 GMT

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

Re: Load 3 years data per sales person

sasikanth — Tue, 15 Jun 2021 06:48:34 GMT

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;