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".
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.
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))
That should work. Make sure you added ChangeNewValue to Table6.
You could also try:
where match(ChangeNewValue,'Quarantined - DSC')
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)
Can you share your qvw or mock up a dummy one that represents your data?
Please have a look. This is the best I can do for now.
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
;
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?
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.