Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm not sure how to select the most recent value for presentation.
I have a field called [ChangeDate] and a load script that looks like this.
Left keep
Table6:
Load
ChangeKeyValue as ITMSNumber,
IF(ChangeNewValue='Quarantined - DSC', date(Today()) - Floor(date(TIMESTAMP(ChangeDate, 'YYYY-MM-DD hh.mm.ss.fff')))) as DaysPassed;
SQL SELECT ChangeKeyValue,
ChangeNewValue,
ChangeDate
FROM MPL.dbo."tblCHANGE";
All I want is the most recent [ChangeNewValue] if they read "Quarantined - DSC".
You could try.
SummaryTable:
ITMSNumber,
max([ChangeDate] as ChangeDateMax
resident YourTable
group by ITMSNumber
;
can you write it exactly how it should go in there or at least elaborate a bit?
Essentially create a summary table that has just the max changed date you're looking for.
So after your code:
You would add:
resident Table6
This would give you a summary table with max changed date for each ITMSNumber. It would look something like this.
It's coming back with "ChangeDate" not found...
Sorry you would need to change your script to have it included like so :
ChangeDate,
Ok it worked. Now how can I put a count of all of the most recent "Quarantined - DSC" that I have into a text box?
=count(ChangeDateMax) is one way.
That is way off. It says 3763 when there is only 37 Quarantined - DSC in the data.
I keep making erroneous assumptions. I apologize. You're pulling all ChangeNewValues into your summary table.
Add in the ChangeNewValue into your table6 and then filter to quarantined with summary table generation.
IF(ChangeNewValue='Quarantined - DSC', date(Today()) - Floor(date(TIMESTAMP(ChangeDate, 'YYYY-MM-DD hh.mm.ss.fff'))));
where ChangeNewValue='Quarantined - DSC'
then your formula for your text box would be =count(ChangeDateMax)
