Select only the most recent values

[#1]

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".

[#2]

It says "garbage after statement" and underlines where ChangeNewValue='Quarantined - DSC' in red.

Note that I don't want only a count of all the most recent quarantined, I want to have a count of all the quarantined under 5 days, then over 5 days and under 15, then over 15 days. That's why I have the DaysPassed variable in there.

[Anonymous]

Not to confuse you  but now that I know you're trying to get it in a text box - as an alternative, to go without the summary table you could do this in your script

Left keep

Table6:

Load

ChangeKeyValue as ITMSNumber,

ChangeDate,

ChangeNewValue,

IF(ChangeNewValue='Quarantined - DSC', date(Today()) - Floor(date(TIMESTAMP(ChangeDate, 'YYYY-MM-DD hh.mm.ss.fff'))));

SQL SELECT ChangeKeyValue,

ChangeNewValue,

ChangeDate

FROM MPL.dbo."tblCHANGE";

and then this in your text box.

=count(aggr(max({<ChangeNewValue={'Quarantined - DSC'}>}ChangeDate),ITMSNumber))

[Anonymous]

That should work.  Make sure you added ChangeNewValue to Table6.

You could also try:

where match(ChangeNewValue,'Quarantined - DSC')

[#5]

ok. It says 9... I'm sorry but that didn't accomplish what I was looking for. What is the 9 for?

Quarantined (Days)

> 15 (Text box), > 5 < 15 (Text box), < 5 (Text box)

[Anonymous]

Can you share your qvw or mock up a dummy one that represents your data?

[#7]

Please have a look. This is the best I can do for now.

[Anonymous]

I see.  I'm assuming you only need quarantined so use this:

Left keep

Table6:

Load

ChangeKeyValue as ITMSNumber,

ChangeDate,

ChangeNewValue,

IF(ChangeNewValue='Quarantined - DSC', date(Today()) - Floor(date(TIMESTAMP(ChangeDate, 'YYYY-MM-DD hh.mm.ss.fff'))));

SQL SELECT ChangeKeyValue,

ChangeNewValue,

ChangeDate

FROM MPL.dbo."tblCHANGE";

inner join(Table6)

Load

ITMSNumber,

ChangeNewValue,

max([ChangeDate] as ChangeDate

resident Table6

where ChangeNewValue='Quarantined - DSC'

group by ITMSNumber,ChangeNewValue

;

[#9]

Ok I ran the script now how do I get this to add up to 37?

The code I am using is as follows-

=Count({1<DaysPassed = {'<5'}>} ITMSNumber)

Also, I see that you don't have DaysPassed in your script. Did I do something wrong by keeping it there?

[Anonymous]

Change this:

IF(ChangeNewValue='Quarantined - DSC', date(Today()) - Floor(date(TIMESTAMP(ChangeDate, 'YYYY-MM-DD hh.mm.ss.fff'))))

to

IF(ChangeNewValue='Quarantined - DSC', date(Today()) - Floor(date(TIMESTAMP(ChangeDate, 'YYYY-MM-DD hh.mm.ss.fff')))) as DaysPassed

And your expression should work.