topic Re: Load Only the last day of month in QlikView

Load Only the last day of month

ecabanas — Sat, 04 Jun 2016 00:19:30 GMT

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 😉

Re: Load Only the last day of month

swuehl — Sat, 04 Jun 2016 00:31:04 GMT

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

Re: Load Only the last day of month

fkeuroglian — Sat, 04 Jun 2016 00:34:02 GMT

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.

Re: Load Only the last day of month

ecabanas — Sat, 04 Jun 2016 00:38:00 GMT

Brilliant!! many thank's

Re: Load Only the last day of month

beristardj — Mon, 09 May 2022 14:50:08 GMT

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