Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ;
A shorter version:
TEMP
...
;
INNER KEEP (TEMP) LOAD
date(max(Date),'MM-DD-YYYY') as Date
RESIDENT TEMP
GROUP by floor(MonthEnd(Date));
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;