topic Re: Select only the most recent values in QlikView

Select only the most recent values

Thu, 19 Jan 2017 19:03:48 GMT

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

Re: Select only the most recent value

Anonymous — Thu, 19 Jan 2017 19:13:55 GMT

You could try.

SummaryTable:

Load

ITMSNumber,

max([ChangeDate] as ChangeDateMax

resident YourTable

group by ITMSNumber

;

Re: Select only the most recent values

Thu, 19 Jan 2017 19:16:35 GMT

can you write it exactly how it should go in there or at least elaborate a bit?

Re: Select only the most recent values

Anonymous — Thu, 19 Jan 2017 19:31:33 GMT

Essentially create a summary table that has just the max changed date you're looking for.

So after your code:

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

You would add:

SummaryTable:

Load

ITMSNumber,

max([ChangeDate] as ChangeDateMax

resident Table6

group by ITMSNumber

;

This would give you a summary table with max changed date for each ITMSNumber. It would look something like this.

ITMSNumber	ChangeDateMax
1A	01/01/2016
2B	06/15/16
4C	12/12/16

Re: Select only the most recent values

Thu, 19 Jan 2017 19:46:54 GMT

It's coming back with "ChangeDate" not found...

Re: Select only the most recent values

Anonymous — Thu, 19 Jan 2017 19:49:39 GMT

Sorry you would need to change your script to have it included like so :

Left keep

Table6:

Load

ChangeKeyValue as ITMSNumber,

ChangeDate,

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

Re: Select only the most recent values

Thu, 19 Jan 2017 19:52:16 GMT

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?

Re: Select only the most recent values

Anonymous — Thu, 19 Jan 2017 20:00:06 GMT

=count(ChangeDateMax) is one way.

Re: Select only the most recent values

Thu, 19 Jan 2017 20:01:35 GMT

That is way off. It says 3763 when there is only 37 Quarantined - DSC in the data.

Re: Select only the most recent values

Anonymous — Thu, 19 Jan 2017 20:11:12 GMT

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.

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

SummaryTable:

Load

ITMSNumber,

max([ChangeDate] as ChangeDateMax

resident Table6

group by ITMSNumber

where ChangeNewValue='Quarantined - DSC'

;

then your formula for your text box would be =count(ChangeDateMax)

Re: Select only the most recent values

Thu, 19 Jan 2017 20:15:57 GMT

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.

Re: Select only the most recent values

Anonymous — Thu, 19 Jan 2017 20:17:01 GMT

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))

Re: Select only the most recent values

Anonymous — Thu, 19 Jan 2017 20:19:55 GMT

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

You could also try:

where match(ChangeNewValue,'Quarantined - DSC')

Re: Select only the most recent values

Thu, 19 Jan 2017 20:24:27 GMT

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)

Re: Select only the most recent values

Anonymous — Thu, 19 Jan 2017 20:26:39 GMT

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

Re: Select only the most recent values

Thu, 19 Jan 2017 21:00:46 GMT

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

Re: Select only the most recent values

Anonymous — Thu, 19 Jan 2017 21:20:27 GMT

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

;

Re: Select only the most recent values

Fri, 20 Jan 2017 13:43:40 GMT

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?

Re: Select only the most recent values

Anonymous — Fri, 20 Jan 2017 16:57:15 GMT

Change this:

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

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

And your expression should work.