Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Analyst240
New Contributor III

I need to create a custom Parameter.

Hello,

I normally use a filter pane to show users options to filter on in qlik sense. Though the data I am trying to filter this time is location data and can be assigned to more than one location. Examples of data strings can be as follows.

"A"

"B"

"A,B"

If these were the only options I would be fine but we have around 10 locations. As you can see this leads to many many many options. What I want to do is set up something like we would see in SSRS where I can manually create a number of Options for a user to choose from and try comparing that data to the Location field in my report. So If I select A it will select all rows of data where the location string includes A in it.  Or in simpler terms I want to pull all data where A is included in its assigned locations. 

Is this sort of comparison possible? I have yet to run into a case where I need to find if a string is located within a field for filtering in qlik sense but its something that can be done pretty easily in SQL and SSRS. I have yet to find it after searching here and other sites but maybe I am looking this up wrong..

 

Thanks!

 

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: I need to create a custom Parameter.

The best approach is to create a new table in the load, splitting the location strings into individual location values.

Locations:
LOAD
LocationString,
SubField(LocationString, ',') as Location
Resident MyData;

-Rob

View solution in original post

5 Replies
MVP & Luminary
MVP & Luminary

Re: I need to create a custom Parameter.

The best approach is to create a new table in the load, splitting the location strings into individual location values.

Locations:
LOAD
LocationString,
SubField(LocationString, ',') as Location
Resident MyData;

-Rob

View solution in original post

Partner
Partner

Re: I need to create a custom Parameter.

agree  with  rob. thats the best approach

Other options are not as efficient e.g. you could use wildcard characters and use in set analysis

Analyst240
New Contributor III

Re: I need to create a custom Parameter.

Thanks Rob. That sounds like a sound plan.
Analyst240
New Contributor III

Re: I need to create a custom Parameter.

Now after this data is divided its going to split a single row of data into multiple right? After that how would I create a filter to pull in all rows of the specified location? I like the filter bar but its usually based on data that already exists in my data set.

 

Thanks,

 

MVP & Luminary
MVP & Luminary

Re: I need to create a custom Parameter.

Yes, each individual Location value will generate a separate row, so yes, multiple.  Your script might look something like this:

Sales:
LOAD * Inline [
Product, Quantity, LocationList
Shoes, 100, "A,B"
Shoes, 400, "A"
Pants, 200, "A"
Pants, 50, "B,D"
Socks, 3000, "A,B,D, Z"
]
;

Locations:
LOAD Distinct
  LocationList,
  Trim(SubField(LocationList, ',')) as Location
Resident Sales;

And that will generate a data model like:

2019-10-09_18-44-35.png

where table Sales has 5 rows and table Locations has 9 rows.  Use field "Location" in your filterpane.  Selecting any Location will associate (filter) to those rows with a matching LocationList. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com