I need to create a custom Parameter.

[Analyst240]

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!

[rwunderlich · Accepted solution]

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]

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]

Thanks Rob. That sounds like a sound plan.

[Analyst240]

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]

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:

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