Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
ecabanas
Creator II
Creator II

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 😉

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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


View solution in original post

4 Replies
swuehl
MVP
MVP

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


fkeuroglian
Partner - Master
Partner - Master

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
Creator II
Creator II
Author

Brilliant!! many thank's

beristardj
Creator
Creator

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