Load Only the last day of month

[ecabanas]

Hi I have the following data and I want to extract for every month and item ONLY the last month day stock data in the loading script.

Many thank's guys

PS: In the excell attached there is only one item 😉

[swuehl · Accepted solution]

Maybe like

INPUT:

LOAD DateStock,

     ItemId,

     stock

FROM

[10357795.xls]

(biff, embedded labels, table is Sheet1$);

RIGHT JOIN

LOAD ItemId, Date(Max(DateStock)) as DateStock

RESIDENT INPUT

GROUP BY ItemId, MonthName(DateStock);

DateStock	ItemId	stock
10/31/2015	10357795	27
11/30/2015	10357795	11
12/31/2015	10357795	75
1/31/2016	10357795	71
2/29/2016	10357795	63
3/31/2016	10357795	63
4/30/2016	10357795	59
5/31/2016	10357795	55
6/4/2016	10357795	55

[fkeuroglian]

Hi Eduard

You can create a flag that help you to know if the date is the last day of every month

you can use this in your script

if(CalendarDate=Date(Floor(MonthEnd(CalendarDate))),1,0) as end_Flag

Then you can do a resident load that only bring data if END_FLAG = 1

you will load only the last day of every month

Hope help!

Fernando K.

[ecabanas]

Brilliant!! many thank's

[beristardj]

muchas gracias, preciso, puntual facil de implementar, pero en mi caso todavia estoy buscando entender la teoria detras de esta estupenda solucion, saludos desde Mexico