topic Re: Dynamically applied filter in load script in QlikView

Dynamically applied filter in load script

Mon, 28 Mar 2016 07:16:45 GMT

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

Re: Dynamically applied filter in load script

swuehl — Mon, 28 Mar 2016 18:28:37 GMT

Maybe something like

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

SELECT

...

FROM YourTable

WHERE REGION = '$(vRandRegion)';

Re: Dynamically applied filter in load script

Tue, 29 Mar 2016 05:28:38 GMT

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.

Re: Dynamically applied filter in load script

amit_saini — Tue, 29 Mar 2016 05:41:57 GMT

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,

Re: Dynamically applied filter in load script

Kushal_Chawda — Tue, 29 Mar 2016 05:44:38 GMT

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

Re: Dynamically applied filter in load script

Tue, 29 Mar 2016 06:29:58 GMT

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

MayilVahanan — Tue, 29 Mar 2016 06:38:21 GMT

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.

Re: Dynamically applied filter in load script

Tue, 29 Mar 2016 06:55:18 GMT

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?

Re: Dynamically applied filter in load script

jyothish8807 — Tue, 29 Mar 2016 07:25:40 GMT

Hi Mayil,

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

Regards

Re: Dynamically applied filter in load script

Kushal_Chawda — Tue, 29 Mar 2016 07:53:49 GMT

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

Re: Dynamically applied filter in load script

MayilVahanan — Tue, 29 Mar 2016 08:02:35 GMT

Try like this

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

Let vName = Chr(39) & Replace('$(vName)', ',', Chr(39)&','&Chr(39)) & Chr(39);

CustomerInfo:

Load * from yourtable Where MixMatch([Customer A/c Name], $(vName));

User need to enter like smith,jones etc.

Re: Dynamically applied filter in load script

swuehl — Tue, 29 Mar 2016 13:56:59 GMT

Create a variable vUserSelect in your UI

=Only(RegionField) // or concat field values

Then, in your script & on reload, test if the variable shows a value and filter your table

Let vWhere = If(Isnull(vUserSelect),'1=1', 'Region = ' & chr(39) & vUserSelect & chr(39));

LOAD

...

FROM ...

WHERE $(vWhere);