Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

ecabanas
Contributor

Load Only the last day of month

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 😉

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Load Only the last day of month

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/20151035779527
11/30/20151035779511
12/31/20151035779575
1/31/20161035779571
2/29/20161035779563
3/31/20161035779563
4/30/20161035779559
5/31/20161035779555
6/4/20161035779555


3 Replies
MVP
MVP

Re: Load Only the last day of month

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/20151035779527
11/30/20151035779511
12/31/20151035779575
1/31/20161035779571
2/29/20161035779563
3/31/20161035779563
4/30/20161035779559
5/31/20161035779555
6/4/20161035779555


Highlighted
Partner
Partner

Re: Load Only the last day of month

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
Contributor

Re: Load Only the last day of month

Brilliant!! many thank's