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

[Anonymous]

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.

[sunny_talwar · Accepted solution]

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

[Anonymous]

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

[sunny_talwar]

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

hic's content‌

[Anonymous]

Thank you!

[pablolabbe]

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

[Anonymous]

Sure Pablo thank you for the note!

[Anonymous]

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