Value per max date

[kprotrka]

I want to show the values(string values) per max date grouped by ID.

For example:

ID       DATE                            CLUSTER

1           01.01.2020                     A

1            01.01.2018                    B

1          01.01.2017                       B

I would like to have the following results:

ID       DATE                            CLUSTER

1           01.01.2020                     A

1            01.01.2018                    A

1          01.01.2017                       A

I want to have just one cluster from the max date per ID.

[Kushal_Chawda]

@kprotrka  may be this

Data:
LOAD * Inline [
ID, Date, Cluster
1, 01/01/2021,B
1,02/01/2021,C
1,03/01/2021, A];

left join(Data)
LOAD ID,
     max(Date) as Date,
     1 as Max_Date_Flag
Resident Data
Group by ID;

left join(Data)
LOAD ID,
     Cluster as Cluster_new
Resident Data
where Max_Date_Flag=1;

DROP Fields Cluster,Max_Date_Flag;

[kprotrka]

i got the same results....attached screenshot.

Could you tell me what could be the cause?

[Kushal_Chawda]

@kprotrka If you are using the same script as I written then it should work. May you are doing something wrong. Please send your script 

[kprotrka]

Attached my qvf file

[kprotrka]

@Kushal_Chawda do you have any idea how to solve it?

[Kushal_Chawda]

@kprotrka  which part of the script need to be included?

[kprotrka]

@Kushal_Chawda i want to add this code from your side. But for the cluster i still get different results....

[Kushal_Chawda]

@kprotrka  looking at your script, you need to change below code. when you are calculating max date. date name should be the same as in table

load

ABRECHNUNGSEINHEIT,MAX(date(Statistik.AB_DATUM)) as Statistik.AB_DATUM, 1 as Max_Date_Flag

[kprotrka]

@Kushal_Chawda  i still get the same results....