Dynamically applied filter in load script

[#1]

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

[swuehl]

Maybe something like

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

SELECT 

...

FROM YourTable

WHERE REGION = '$(vRandRegion)';

[#3]

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]

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

[Kushal_Chawda]

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

[#6]

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.

[MayilVahanan · Accepted solution]

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.

[#8]

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]

Hi Mayil,

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

Regards

KC

[Kushal_Chawda]

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