Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

hemasubhash
New Contributor II

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

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:

Question1.png

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

Question.png

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


question2.png


Hope someone can help.


Tags (1)
1 Solution

Accepted Solutions

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

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

View solution in original post

7 Replies

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

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

View solution in original post

hemasubhash
New Contributor II

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

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.

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

hic's content

hemasubhash
New Contributor II

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

Thank you!

Luminary
Luminary

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

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

hemasubhash
New Contributor II

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

Sure Pablo thank you for the note!

Highlighted
hemasubhash
New Contributor II

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

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