Expression Help

[mahitham]

Hi,

Can any one please help me on below requirement.

I have the Source like below

From The Date field Derived [Comparision Date] like below

main:
LOAD
    "Date" as ReportDate,  
    ID,
    Name
FROM [lib://test/Add,Remove,Replace.xlsx]
(ooxml, embedded labels, table is Sheet1);

comp:
load   "ReportDate" as ComparisionDate

Resident main;

Requirement:

By comparing  Max Report Date 13/10/2019 with 12/10/2019 I have to create a Table in UI like below

ID         Original Name         Renamed Name

5           E                                      M

By comparing Max Report Date 13/10/2019 with 11/10/2019 I have to create a Table in UI like below

ID         Original Name         Renamed Name

5           E                                      M

7           G                                      H

By using below expression getting "Renamed Name" column

Only(DISTINCT {<[ ID] = {"=Name <> Only({<[Report Date] = p([Comparision Date])>} Name) and Len(Trim(Name)) > 0 and Len(Trim(Only({<[Report Date] = p([Comparision Date])>} Name))) > 0"}>} Name)

But  I am not able to get the "Original Name" Column please help me to get Original Column name.

Thanks in advance

[DavidM]

Why don't you add original and new name fields to the table? Like this:

left join (main)
load   Min("ReportDate") as "OriginalDate",

ID,

Name as [OriginalName]

Resident main

group by ID,Name;

left join (main)
load   Max("ReportDate") as "NewestDate",

ID,

Name as [LatestName]

Resident main

group by ID,Name;

[mahitham]

Hi @DavidM 

Could you please provide the UI Solution. I am not able to change the backend script.