filtering only last record of each month in script

[m4u]

Hi all,

i have a data source that contains some measure per date (sometimes not all dates are updated on my data source).

i need to import only the last knon measure for each month.

my datasource contains 2 columns: Date,Measure.

an example of what i want to do:

original table:
Date Measure
01/04/12 3
02/04/12 3.5
03/04/12 4.1
07/04/12 3

01/05/12 3
02/05/12 6
03/05/12 4

table after etl process:
07/04/12 = 3
03/05/12 = 4

any idea how to implement this in script?

meany thanks,

Shlomo

[Miguel_Angel_Baeyens · Accepted solution]

Hi Shlomo,

Something like the following should work:

Data:
LOAD *, Month(Date) AS Month INLINE [
Date, Measure
01/04/12, 3
02/04/12, 3.5
03/04/12, 4.1
07/04/12, 3
01/05/12, 3
02/05/12, 6
03/05/12, 4
];
LastValue:
LOAD FirstSortedValue(Measure, -Date) AS LastMeasureByMonth,
 Month
RESIDENT Data
GROUP BY Month;

Hope that helps.

Miguel

[m4u]

Hi Miguel, meany thanks it was helpful.

[somenathroy]

Another option:

A:

LOAD * Inline

[Date, Measure

01/04/12 ,3

02/04/12 ,3.5

03/04/12 ,4.1

07/04/12 ,3

01/05/12 ,3

02/05/12 ,6

03/05/12 ,4];

Right Join

LOAD Max(Date) as Date

Resident A

Group By Month(Date);

Regards,

Somenath

[IAMDV]

Thanks Miguel.

Hypothetical Question : What happens if we have two dates with same values and FirstSortedValue returns Null value isn't it? How do we overcome this issue?

Thanks in advance.

Cheers,

DV