topic Re: Add entry Others to Listbox to filter data in QlikView

Add entry Others to Listbox to filter data

Anonymous — Mon, 07 Dec 2015 17:24:28 GMT

Hello,

i have a table with entries and a table with categories.

category-value = 1 assigend

category_value = 0 = not assigened

entry:

id, description
1, 'entry 1'
2, 'entry 2'
3, 'entry 3'

category:

id, category_name, category_value
1, 'category A', 1
1, 'category B', 1
1, 'category C', 0
2, 'category A', 1
2, 'category B', 0
2, 'category C', 0
3, 'category A', 0
3, 'category B', 0
3, 'category C', 0

I want to use the field category_name as a Listbox control to filter entries.

Furthermore I use an set analysis function like COUNT( {$<category_value={1}>} id)

to Count only entries with at least one category.

Is it possible to add an value 'Others' to the listbox control

to select only entries which has no assigend categories SUM(category_value) = 0 ?

I could try with the SQL query....

SELECT
a.id
a.category_name,
a.category_value,
1 AS SUM_catValue
FROM
category a

UNION

SELECT
a.id,
'Others' AS category_name,
1 AS category_value,
SUM(category_value) AS SUM_catValue

FROM
category a
GROUP BY a.id, 'Others', 1
HAVING SUM(category_value) < 1

But can I also do it without manipulating the query?

Thanks and best regards.

Re: Add entry Others to Listbox to filter data

sunny_talwar — Mon, 07 Dec 2015 17:58:45 GMT

Try this list box expression:

=Aggr(If(Sum({1} category_value) > 0, Only({1} category_name), 'Others'), category_name)

Re: Add entry Others to Listbox to filter data

Kushal_Chawda — Mon, 07 Dec 2015 18:13:28 GMT

may be try this expression

=count({<id = {"=sum(Category_value)=0"}>}id)

Re: Add entry Others to Listbox to filter data

Anonymous — Tue, 08 Dec 2015 08:42:45 GMT

Thanks,

but with an expression I only get a second column and the Expression is calculated for each entry.

I Need a completely new entry under the categories A, B, C

any idea how I can add entries to a listbox without changing the data table?

Re: Add entry Others to Listbox to filter data

sunny_talwar — Tue, 08 Dec 2015 15:44:41 GMT

May be make that manipulation in the script? and what would you filter when a user selects Others?

Re: Add entry Others to Listbox to filter data

maxgro — Tue, 08 Dec 2015 16:08:56 GMT

maybe an option to use the search in the listbox

=sum(category_value)=0