Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Dynamically applied filter in load script

How can we load data in qlikview with dynamically required filters applied.

i.e I have Sales data corresponding to different regions in SQL DB.

I want to develop a qlikview report which should load data for a random region that I specify dynamically.

The load query should hit the DB with filter as dynamically given region

1 Solution

Accepted Solutions

Re: Dynamically applied filter in load script

Hi

If you want to enter value for region dynamically by user input. Try like this

Let vRegion = Input('Enter region', 'Input box');

RegionInfo:

Load * from yourtable Where Region = '$(vRegion)';

While reloading, its ask user to enter value for region.  And it display those region information alone.

11 Replies
MVP
MVP

Re: Dynamically applied filter in load script

Maybe something like

Let vRandRegion = Pick(CEIL(RAND()*3), 'West','East','South');

SELECT 

...

FROM YourTable

WHERE REGION = '$(vRandRegion)';

Not applicable

Re: Dynamically applied filter in load script

By Random, I meant that region must be specified dynamically by the user.

When the user opens the report, he must be provided with a drop down which has all the possible values present in that region column of the DB table.

The selected region in that drop down must be applied as a filter in the load script and fetch only that region data.

Is this possible.

amit_saini
Honored Contributor III

Re: Dynamically applied filter in load script

Hi,

Like this???

LOAD Country,

     City,

     Pin,

     Marks

FROM

(biff, embedded labels, table is Sheet1$)

where Match(City,'DELHI','PUNE');  (Similar condition you can give for Region)

Thanks,

AS

Re: Dynamically applied filter in load script

Define the region to be loaded in inline table

Region:

Load *, concat(chr(39)&Region&chr(39),',') as Region inline [

REGION

West

North

South ];

let vSelectRegion = peek('Region',0,'Region');

Table:

SQL SELECT *

FROM TABLE

WHERE REGION IN($(vSelectRegion));

Not applicable

Re: Dynamically applied filter in load script

Thanks Amit.

But here, Delhi and Pune are hardcoded values given in filters. I need them to be based on what user gives while opening the report. Just like Prompts in Microstrategy reporting tool.

The Qlikview should hit the DB with only the values which user gives at runtime.

Re: Dynamically applied filter in load script

Hi

If you want to enter value for region dynamically by user input. Try like this

Let vRegion = Input('Enter region', 'Input box');

RegionInfo:

Load * from yourtable Where Region = '$(vRegion)';

While reloading, its ask user to enter value for region.  And it display those region information alone.

Not applicable

Re: Dynamically applied filter in load script

Thanks Mayil. I tried that and was successful.

I could give multiple values in input box and load data for those inputs values.

Is there any flexibility that user can see all the possible values to the field (in this case Region) and select from that list?

jyothish8807
Honored Contributor II

Re: Dynamically applied filter in load script

Hi Mayil,

Nice solution . If i want to enter multiple filters in input box, then how should i proceed ?

Regards

KC

Best Regards,
KC

Re: Dynamically applied filter in load script

Then i think you need to enter the Value like below in input box

'North','South'

then below script

RegionInfo:

Load * from yourtable Where match(Region ,$(vRegion));

I have not QV to check this but you can try

Community Browser