Return a string in a Field from looking up a string in another Field

[richard_gale]

Hi - I have what I think should be a relatively simple task to write a chart expression for, but have been unable to find a solution from searches. Hoping somebody can help.

I have a table with two columns /Fields - Baseline and Release_Date

Baseline                           Release_Date

2022 Dec BOD 4             20/11/2022

2022 Dec BOD 5             18/1/2023

I already have a Variable that returns the latest date, 18/1/2023, from the Release_Date Field - vLatest_Release_Date = Date(Max([Release_Date])) - and I need an expression that returns the '2022 Dec BOD 5' by 'looking up' the Variable in the Baseline Field

The Baseline Field format can have many different formats - the expression just needs to return what's there based on the Release Date.

Many thanks, Richard

[Vegar]

Try using the firstsortedvalue() function.

=FirstSortedValue(Baseline,-Release_Date)

[richard_gale]

Thanks for the suggestion but unfortunately doesn't work

Cheers, Richard

[Vegar · Accepted solution]

I used this script

LOAD
   Baseline,
   date#(Release_Date, 'D/M/YYYY') as Release_Date
inline [
Baseline, Release_Date
2022 Dec BOD 4, 20/11/2022
2022 Dec BOD 5, 18/1/2023
];

and with that datamodel I was able to get "2022 Dec BOD 5" using FirstSortedValue(Baseline, -Release_Date)

See attached app

[richard_gale]

Thank you, Vegar!

So the script 'date#(Release_Date, 'D/M/YYYY') as Release_Date inline' is converting the date to a numerical value, just like Excel does if you change a date to a Number format?

Thanks again for your help,

Richard

[Vegar]

Yes, date#() i interpretation function. It interpretat the given string value (1st parameter) as a date with the given dateformat (2nd parameter). 

[sidhiq91]

@richard_gale  Hello Richard, In case if you are looking for something at the script level. Please see the code below:

NoConcatenate
Temp:
Load Baseline, Date(Date#(Release_Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Release_Date inline [
Baseline,Release_Date,0,

2022 Dec BOD 4,20/11/2022

2022 Dec BOD 5,18/1/2023

];

NoConcatenate
Temp1:
Load *
Resident Temp
Order by Release_Date Desc;

Let Vmax=Peek('Release_Date',0,'Temp1');

Drop table Temp;

NoConcatenate
Temp2:
Load Baseline
Resident Temp1
where Release_Date='$(Vmax)';

Drop table Temp1;

Exit Script;