Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to find out the max date for each year month. I have date like this:
21/9/2015, |
5/9/2015, |
12/9/2015, |
1/10/2015, |
5/10/2015, |
So i want to load only max date for every month year. Expected output should be :
21/9/2015
5/10/2015
Thanks
HI,
Will the attached file serve the purpose
try this:
t1:
Load
Mid(Date,Index(Date,'/',2)+1)&'/'&TextBetween(Date,'/','/')&'/'&Mid(Date,1,Index(Date,'/')-1) as Date,
Sales,
Mid(Date,Index(Date,'/',2)+1)&'/'&TextBetween(Date,'/','/') as YM
Inline
[
Date, Sales
21/9/2015, 100
5/9/2015, 200
12/9/2015, 300
1/10/2015, 400
5/10/2015, 500
];
Filter:
LOAD Date(Max(Date),'YYYY/M/D') as Date1 Resident t1 Group by YM;
NoConcatenate
t2:
LOAD * Resident t1 Where Exists(Date1,Date);
DROP Tables t1,Filter;
DROP Field YM;