Loading most recent records of a field with respect to date

[Aspiring_Developer]

Hi,

I want to load only most recent records for milestone:-

Milestone  Date         Value

M1                20201      10

M1                20202    20

M2               20202   30

From Source..

I want to achiever that when i take the Mileston field in front end, it should give only latest record of M1 ie. M1 =20

I think we can achieve it using order  by date and peek but not sure. please help.

Thanks

[Taoufiq_Zarra]

@Aspiring_Developer  in load Script or UI ?

for example in UI you can use FirstSortedValue()

in load Script you can use group by and min or :

Data:

load * inline [

Milestone,Date,Value
M1,20201,10
M1,20202,20
M2,20202,30
];

Final:
noconcatenate

load *,Milestone as k resident Data where not Exists(k,Milestone) order by Date DESC ;

drop table Data;
drop field k;

output :

[Aspiring_Developer]

@Taoufiq_Zarra  I have show it in the load script. But i will take this milestone field in the chart as dimension without date . So , how can we get the most recent milestone value without taking date as dimension . please help

[Aspiring_Developer]

@Taoufiq_Zarra  Please see the data for your reference

We have dates till 201706 and i want only those M1, M2 values whose date is 202006 . Please help

[Taoufiq_Zarra]

can you develop more ? what about M3,M4 ... ?

202006 is the max date ? or fixed value ?

[Aspiring_Developer]

@Taoufiq_Zarra  The above one worked but i have multiple "Project Codes" against each Milestone . So is there a way we can get the latest milestone for each project with respect to latest Date. Because as of now it is giving me less records like below :-

Is there any way we can get the project codes as well ?

[Aspiring_Developer]

@Taoufiq_Zarra  Yes 202006 is maximum date

[Kushal_Chawda · Accepted solution]

@Aspiring_Developer  try below

Data:
Load Milestone,
     ProjectCode,
     date(date#(Date,'YYYYMM'),'YYYYMM') as Date
FROM Data;

left join(Data)
load  date(max(Date),'YYYYMM') as Date
      1 as MaxDate_Flag
resident Data;

Now you can use the flag created in measure you use it in chart so your milestone and projectcode will get automatically filtered 

=sum({<MaxDate_Flag={1}>}Value)

[Aspiring_Developer]

@Kushal_Chawda 

Sorry, please see the below as i am still facing the issue please.

Project Code:-C06965   , New_M-:-M1, Date: -201706 till 201712. So it should return the value for 201712 as it is the most recent date for this Milestone and project code:-

Attached the excel for your reference. please help as i am not able to understand how to do this.

[Kushal_Chawda · Accepted solution]

@Aspiring_Developer  looks like you need milestone wise max date and not the max date for all milestone. then change the script as below

Data:
Load Milestone,
     ProjectCode,
     date(date#(Date,'YYYYMM'),'YYYYMM') as Date
FROM Data;

left join(Data)
load  Milestone,
      date(max(Date),'YYYYMM') as Date
      1 as MaxDate_Flag
resident Data
group by Milestone;

Your expression will remain the same