topic Re: Selecting the maximum date value from the data model based on Non Null values in a field. in QlikView

Selecting the maximum date value from the data model based on Non Null values in a field.

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm very new to QlikView and I'm just starting to get head and tail of it; for the purpose of creating a dashboard, I need to display some KPIs corresponding the latest date/week where non-NULL data is available in certain fields in linked tables.

Here is the data model:

Here's the pseudocode:

How do I go about doing this.."?

So based on the sample data below, I need vMaxDate_FiltrationPlant to be selected as '2016-10-04' and vMaxWeek_FiltrationPlant to be selected as 41..

basically implement this pseudocode:

SET vMaxDate_FiltrationPlant = max(SampleDate)

SET vMaxWeek_FiltrationPlant = max(Week)

where PAR_Filtrationplant <> NULL

And based on the sample data from another fact table I need to vMaxDate_Bacteriatest to be selected as '2018-02-23'

basically, perform this:

SET vMaxDate_Bacteriatest = max(SampleDate)

where FRNUM <> NULL

Hope someone can help.

Re: Selecting the maximum date value from the data model based on Non Null values in a field.

sunny_talwar — Mon, 26 Feb 2018 13:04:22 GMT

May be something like this

LOAD Max(SampleDate) as MaxSampleDate,

Max(Week) as MaxWeek

Resident ....

Where Len(Trim(PAR_Filtrationplant)) > 0;

LET vMaxDate_FiltrationPlant = Peek('MaxSampleDate');

LET vMaxWeek_FiltrationPlant = Pee('MaxWeek');

Re: Selecting the maximum date value from the data model based on Non Null values in a field.

Anonymous — Mon, 26 Feb 2018 18:35:10 GMT

Yayy thank you, that worked! Could you please suggest some free online resources to learn the basics of coding in QlikView.

Re: Selecting the maximum date value from the data model based on Non Null values in a field.

sunny_talwar — Mon, 26 Feb 2018 18:44:08 GMT

You can go through HIC's blog to learn a lot of things

hic's content‌

Re: Selecting the maximum date value from the data model based on Non Null values in a field.

Anonymous — Mon, 26 Feb 2018 20:11:04 GMT

Thank you!

Re: Selecting the maximum date value from the data model based on Non Null values in a field.

pablolabbe — Tue, 27 Feb 2018 19:31:27 GMT

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others

Re: Selecting the maximum date value from the data model based on Non Null values in a field.

Anonymous — Tue, 27 Feb 2018 20:21:29 GMT

Sure Pablo thank you for the note!

Re: Selecting the maximum date value from the data model based on Non Null values in a field.

Anonymous — Tue, 27 Feb 2018 21:11:04 GMT

Although I'm unable to see the button that says Correct answer in the answer that was provided. 😐