Rolling over 6 months in load script, deleting older entries

[#1]

Hi,

I created a new table from my existing data:

TERRITORY_ID	MONTH	Plan
a	201310	63,00 €
a	201311	78,00 €
a	201312	28,00 €
a	201401	509,00 €
a	201402	44,00 €
a	201403	342,00 €
b	201310	212,00 €
b	201312	246,00 €
b	201401	39,00 €
b	201402	56,00 €
b	201403	433,00 €

So in general there is monthly data for each territory, however some territories do not have data for the whole time frame. Now I would like to enhance my script to append new data every month to the table and to delete older data. Appending is not the issue, but how can I figure out if I already have 6 or more than 6 entires per territory and remove the oldest?

Thanks.

[#2]

Hi Jens

So as far as i understand let's say today (201403) is the last reference date.

So first load year(today()) & num(month(today(), '00') to get your current YYYYMM (201403)

Then you load your data coming from other sources

LOAD * FROM table

WHERE EXIST (YYYYMM, DateYouAreCurrentlyLoading)

best regards

chris

[maxgro]

I assume you want to keep only last 6 record by TERRITORY_ID

I add a flag to identify the record to delete

Source:

load * inline [

TERRITORY_ID, MONTH, Plan

a, 201309, 63,00 €

a, 201310, 63,00 €

a, 201311, 78,00 €

a, 201312, 28,00 €

a ,201401, 509,00 €

a, 201402, 44,00 €

a, 201403, 342,00 €

b, 201310, 212,00 €

b, 201312, 246,00 €

b, 201401, 39,00 €

b, 201402, 56,00 €

b, 201403, 433,00 €

];

Table:

NoConcatenate load *,

if(NumRecord>6, 1, 0) as FlagDelete;               // add where NumRecord <= 6 to keep only 6

NoConcatenate

load *,

if(TERRITORY_ID<>Peek(TERRITORY_ID),1, Peek(NumRecord)+1) as NumRecord

Resident Source

order by TERRITORY_ID, MONTH desc;       

DROP Table Source;