Month end records from a resident table

[ajithsachin]

Dear All,

           I have a resident table like below.

TEMP:          

Item	Date	Rate
Item1	01-01-2015	10
Item1	01-15-2015	11
Item1	01-31-2015	10.5
Item2	01-01-2015	20
Item2	01-15-2015	21
Item2	01-31-2015	20.5

from this TEMP table i need to load only month end records.

Regards,

Ajith

[Anonymous]

TEMP:

load

  *

where floor(MonthEnd(Date))  = num(Date)

;

LOAD Item,

    date(date#(Date,'MM-DD-YYYY')) as Date ,

    Rate

FROM

[https://community.qlik.com/thread/160749]

(html, codepage is 1252, embedded labels, table is @1)

;

Data:

NoConcatenate

load

  *

resident TEMP

where floor(MonthEnd(Date))  = num(Date)

;

drop table TEMP ;

[Anonymous]

A shorter version:

TEMP

...

;

INNER KEEP (TEMP) LOAD
date(max(Date),'MM-DD-YYYY') as Date
RESIDENT TEMP
GROUP by floor(MonthEnd(Date));

[maxgro]

another one with peek

temp:

LOAD Item,

    Date,

    Rate

FROM

[https://community.qlik.com/thread/160749]

(html, codepage is 1252, embedded labels, table is @1);

// some other data to test

Concatenate (temp) load * inline [

Item,      Date,      Rate

Item2, 03-01-2015, 14

Item2, 03-15-2015, 13

Item2, 03-31-2015, 12

];

final:

NoConcatenate load *

Resident temp

where Item<>Peek(Item) or year(Date)&num(Month(Date),'00') <> year(Peek(Date)) & num(Month(peek(Date)),'00')

order by Item, Date desc;

DROP Table temp;