pull max(Date) from each month

[jyothish8807]

Hello All,

I need a small help from you regarding a logic.

I am trying to write a where clause to pull the max date for each month in a table.

Suppose i have 10 dates in month jan and 12 dates for month feb, so i want to pull only the max date for jan and feb using a where clause.

i have only one date field and rest are data fields.

Date	Daily
21/9/2015	1
5/9/2015	2
12/9/2015	3
1/10/2015	4
5/10/2015	5

So in out put i want 21/9/2015 and 5/10/2015 to be pulled using a where clause.

Thanks

KC

[jyothish8807]

Hi Sunny,

My group by clause is a very basic one.

LOAD MonthYear,

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

Resident Data

Group by MonthYear;

It should not create issues,I believe the join  is creating the issue. I have almost 3 years data and it is very huge.

I am pulling the latest data of each month by using this logic and ignoring the old data in the dashboard.

Br,

KC

[sunny_talwar]

Just for a kick of it... can you try this

Table:

LOAD Date,

  Daily,

  MonthName(Date) as MonthYear;

LOAD * Inline [

Date, Daily

21/9/2015, 1

5/9/2015, 2

12/9/2015, 3

1/10/2015, 4

5/10/2015, 5

];

Temp:

NoConcatenate

LOAD *

Resident Table

Order By MonthYear;

Join(Table)

LOAD MonthYear,

  Max(Date) as Date,

  1 as Flag

Resident Temp

Group By MonthYear;

DROP Table Temp;

ExtractedData:

LOAD Date as Date1

Resident Table

Where Flag = 1;

[jyothish8807]

Sure sunny, I will give it a try It is an interesting analysis with the sort order

Br,

KC