Where clause to be actioned via dashboard

[mattphillip]

I'm hoping someone may be able to help. Our institution has recently adopted Qlikview and we are in the process of compiling a draft pilot dashboard. We have a need to display certain information which requires the use of a WHERE clause to filter the results as using a list box or dragging over them will select additional records not required. The only way so far I've found to do this (my apologies as we only had our initial training last month), was a script:

LOAD * FROM

(ooxml, embedded labels, table is Sheet1)

WHERE((Qual = 'HNC' And Year = '2') or (Qual = 'HND' And Year = '3'));

Would someone be able to enlighten me on a solution which will mean the full table is loaded, but the WHERE clause is only actioned from the dashboard?

Any help would be much appreciated!

Matt

[datanibbler]

Hi Matt,

generally, this is not possible - QlikView is executed strictly in the order

1) script

2) GUI

You could do it by creating a binary field in the script which is 1 when either of those conditions is met and 0 otherwise - then you could just select on that field on the GUI.

The individual parts of that condition are static?

HTH

Best regards,

DataNibbler

[mattphillip]

Thanks DataNibbler,

Yes the individual part of the condition are static and won't change. My gloriously (Non-)detailed handbook doesn't cover how to do this. Would you be able to suggest how it should be scripted?

Appreciated.

Matt

[vikasmahajan]

You load all data with FLAGS and while display in Dashboard you may use flags in set analysis to view

reports/dashboard

Vikas

[mattphillip]

Thanks for the reply Vikas. Would you be able to elaborate? Our institution is still new to Qlikview.

Any help would be much appreciated.

Matt

[vikasmahajan]

Step 1 : -> Load all data without where condition in qv

LOAD * FROM

(ooxml, embedded labels, table is Sheet1)

Step 2 :- >

Create chart use  where clause in Set Analysis Like

SUM({$<FiscalYear={$(=max(FiscalYear))},Qual ={ 'HNC' } , Max(Year)= 2   >} [Sales Amount])

  // WHERE((Qual = 'HNC' And Year = '2') or (Qual = 'HND' And Year = '3'));

Hope this may help you

Vikas

[mattphillip]

Thanks Vikas, but I'm a bit lost with - SUM({$<FiscalYear={$(=max(FiscalYear))},Qual ={ 'HNC' } , Max(Year)= 2   >} [Sales Amount]) and maybe that's because I didn't clearly state what we'd like to do. We'd like select the relevant records where either of the two conditions (Qual = 'HNC' And Year = '2') or (Qual = 'HND' And Year = '3') are true, so we can then either display them/manipulate them in graphs etc. Perhaps if you could explain what each part of your above line of code does then I might be able to get a better grasp of the solution and how to adapt it?

Thanks,

Matt

P.S. Don't worry - I've already made it clear to my dept head the need for further training!