Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Select only the most recent values

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

18 Replies
atkinsow
Valued Contributor II

Re: Select only the most recent value

You could try.

SummaryTable:

Load

ITMSNumber,

max([ChangeDate] as ChangeDateMax

resident YourTable

group by ITMSNumber

;

Not applicable

Re: Select only the most recent values

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

atkinsow
Valued Contributor II

Re: Select only the most recent values

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.

ITMSNumberChangeDateMax
1A01/01/2016
2B06/15/16
4C12/12/16
Not applicable

Re: Select only the most recent values

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

atkinsow
Valued Contributor II

Re: Select only the most recent values

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

Not applicable

Re: Select only the most recent values

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?

atkinsow
Valued Contributor II

Re: Select only the most recent values

=count(ChangeDateMax) is one way.

Not applicable

Re: Select only the most recent values

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

atkinsow
Valued Contributor II

Re: Select only the most recent values

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)

Community Browser