topic Re: Extract Expression Possible Values in QlikView

Extract Expression Possible Values

Thu, 09 Apr 2015 19:00:45 GMT

I have a scenario where I have a table of values and a separate field in a different table with a comma-separated field with any number of those values.

Example:

Fruit:

Apple

Orange

Peach

Pear

Supplier Table:

Supplier Fruits

John Apple,Orange

Fred Peach

Joe Pear,Apple

I need to have a listbox on Fruit so the users can select one or more fruits, and then apply that list of selected fruits to the "Fruits" field in the Supplier table to determine which suppliers are valid.

I'm most of the way there but can't figure out how to pull the values from a listbox driven by an expression and apply that to a filter on the Fruits field.

I've attached a simple qvw file that shows what I've done to get a dynamic list of value Fruits options, but need that help to get the last bit.

Any help or thoughts are much appreciated.

Mark.

Re: Extract Expression Possible Values

sunny_talwar — Thu, 09 Apr 2015 19:16:58 GMT

Not sure if this is what you are looking for? But have a look (PFA)

Best,

Sunny

Re: Extract Expression Possible Values

sunny_talwar — Thu, 09 Apr 2015 19:18:07 GMT

Updated file (PFA)

Re: Extract Expression Possible Values

maxgro — Thu, 09 Apr 2015 19:38:02 GMT

add a link table

Fruit:

load * inline [

Fruit

Apple

Orange

Peach

Pear

];

SupplierTable:

load * inline [

Supplier ; Fruits

John ; Apple,Orange

Fred ; Peach

Joe ; Pear,Apple

] (delimiter is ';');

Link:

load Supplier, subfield(Fruits, ',') as Fruit Resident SupplierTable;

Re: Extract Expression Possible Values

Thu, 09 Apr 2015 20:05:28 GMT

I like this and it shows me the suppliers, but I still don't see how to pull the valid fruits list (those with 'Grape' in them), and apply that as a filter to the Suppliers table.

This is a simplified example as the real example has about 15 additional fields which are used for various analytics and so I need to be able to pick from the fruit listbox and apply the filter to the supplier table. Thus, the "Valid Fruits" listbox which is expression driven. What I need to do it extract the values from that and apply those as a filter to the Suppliers table so it gets filtered as well (just as the Fruit table does with the Fruit listbox).

Seeing the information is great, but extracting it and applying it as a filter is where I am stuck.

Thanks.

Re: Extract Expression Possible Values

Thu, 09 Apr 2015 20:12:56 GMT

Works like a charm. I think I went through a lot of work I didn't need to in calculating which fields met the criteria. I hadn't thought about creating a link table.

Much thanks!