Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Analyst240
Contributor III
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!

 

Labels (4)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

dplr-rn
Partner - Master III
Partner - Master III

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
Contributor III
Contributor III
Author

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

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,

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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