topic Re: Finding latest date for a field, unless it has a null value in QlikView

Finding latest date for a field, unless it has a null value

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to create a table chart that shows an application and its latest scheduled date.

The data is coming from two separate tables, connected by a hostname key

An application can live on multiple hosts, and those hosts are migrated at different times, so one app can have several "scheduled dates".

What I need to do is find the latest date associated with an application.

But there's a catch... If one of the hosts that an app lives on has not been assigned a scheduled date yet, I need it to reflect that(ideally by displaying "Not Scheduled" in the date field).

So basically if all the hosts an app lives on are scheduled, show me the latest date. If at least one of the hosts an app lives on has not been scheduled, show me some sort of message informing me of that.

So far I have tried using the expression:

=max(aggr([Schedule Date],APPLICATION_NAME,[Host Name]))

But this will still return the latest scheduled date, even if some hosts have not been scheduled. I suppose this has something to do with the way Null values are handled but I am fairly new to QV and am not sure how to go about this.

Visually this is what I need :

Host name | App name | Latest Scheduled Date

host1 | App1 | 01/01/2017

host2 | App1 | 02/02/2017

host3 | App1 | NULL

With my current expression it will return 02/02/2017 since that is the latest date in its eyes, but I need it to return the NULL(or something in its place)

Thanks in advance,

Alex

Re: Finding latest date for a field, unless it has a null value

sunny_talwar — Fri, 01 Sep 2017 15:19:32 GMT

How do you know the Null is the latest, is it because it is associated with host3?

Re: Finding latest date for a field, unless it has a null value

Fri, 01 Sep 2017 15:20:39 GMT

If it has a Null, then it has yet to be scheduled, which would make whatever date is eventually chosen the latest date

Re: Finding latest date for a field, unless it has a null value

Fri, 01 Sep 2017 15:24:14 GMT

the issue is that if any of the hosts of an app are not scheduled, I need to show that it is still waiting to be completely scheduled. But if they are all scheduled, i just need to see the latest date.

This way we can track which apps are on un-scheduled host

Re: Finding latest date for a field, unless it has a null value

sunny_talwar — Fri, 01 Sep 2017 15:26:43 GMT

So essentially when you say Null, there will be 'NULL' written as text or there won't be any value present? Would you be able to share a sample?

Re: Finding latest date for a field, unless it has a null value

Fri, 01 Sep 2017 15:29:09 GMT

There won't be any value present

Re: Finding latest date for a field, unless it has a null value

effinty2112 — Fri, 01 Sep 2017 21:26:24 GMT

Hi Alex,

How do we know if an app is hosted on a particular host? There must be something other than a value for Latest Scheduled Date otherwise we can't differentiate between apps not present and those that are present but haven't been scheduled.

Cheers

Andrew

Re: Finding latest date for a field, unless it has a null value

Sun, 03 Sep 2017 23:01:55 GMT

The app to host relationships are predefined in the datasource, which i am making a RESTful API call to in order to retrieve data. The scheduling information is coming from a different datasource that is used for scheduling host migrations. API call 1 brings in all application-host data, API call 2 brings in all host scheduling information and is linked via the host Inventory #