Show filtered values in one table, and the inverse of the filter in a second table

[Anonymous]

Hey Qlik community, I've got a head-scratcher that I can't seem to find the solution for - not even sure if it's possible! Hoping that you can help me as sort of a last ditch effort.

My app is a list of stores, and the filters that the user can select refer to certain attributes that the store might have.

What I am being asked to build includes a list of all of the stores which have the attributes(easy), right next to a list of all of the stores which do not have the attributes(tough?). I am not sure how I can simultaneously show filtered values, and then in a separate visualization show the inverse.

Furthermore, to complicate things, there are other filters the user can select which should not be affected by this. Meaning, there are five different selections the user can filter by, which will ultimately bring them down to a list of stores remaining in the filter. Then, next to the list of stores, there should be a second list which is affected by four of the filters, and shows the inverse of another filter.

Basically lets say the user has selected A='value1', B='value2', C='value3', D='value4', and E='value5'.

I need to simultaneously have one table that shows all <expression> for which A='value1', B='value2', C='value3', D='value4', and E='value5', and then another table that shows all <expression> for which A='value1', B='value2', C='value3', D='value4', and E <> 'value5'.

Is this possible at all in QlikSense? Any help is appreciated!

[Digvijay_Singh]

You may try in your expressions, the set analysis like below to get excluded values of field E which are not selected.-

Sum({<FieldE=E(FieldE)>}Value)

Please provide sample data and expected output to try further in case it doesn't help.

[nizamsha]

=Sum({$<Name={"*"}-{"e"}>}Amount)

you can try this for 2 table

[nizamsha]

Load * Inline [

ID,Name,Amount

1,A,100

2,B,200

3,c,300

4,d,400

5,e,500

6,f,600

];

1st table sum(Amount) it will have all the data based on selection

2nd table .Sum({$<Name={"*"}-{"e"}>}Amount) it will exclude the particular row

[Anonymous]

Thank you everyone for your help. From what you are showing it sounds like what you have will work, except for the fact that I don't want any kind of aggregation function on it. I don't want the sum, or the count, or the average......I just want the list of all values for which the set analysis is true to appear in a Table object. But from what I have seen, it doesnt seem like Set Analysis is capable of doing that, and I havent been able to make it work on my own.

[Anonymous]

Here is some sample data which maybe can help illustrate the problem:

Load * Inline [

StoreNumber, DivisionName, DistrictName, OwnershipType, SoftwareType

1, Northern, New York, Company Owned, Google

1, Northern, New York, Company Owned, Ask Jeeves

2, Northern, New York, Company Owned, Google

2, Northern, New York, Company Owned, Bing

3, Western, Sacramento, Franchise, Google

3, Western, Sacramento, Franchise, Yahoo

4, Northern, Chicago, Company Owned, Ask Jeeves

4, Northern, Chicago, Company Owned, DuckDuckGo

5, Northern, New York, Company Owned, Ask Jeeves

5, Northern, New York, Company Owned, DuckDuckGo

];

This is supposed to represent a list of the stores and have one row for each "Software" that is installed at the store. This is a simplification of what I am trying to build.

Essentially the user needs to be able to select a SoftwareType (using a TreeMap which contains a "button" for each possible SoftwareType), and see a list of stores in a table  of the stores that have that Software, and then see a list of stores that do not have that Software.  For example the user might select "Google", and then ideally they would see a table with three rows for store 1, 2, and 3, and then next to that they would see a table with rows for store 4 and 5 (because stores 4 and 5 do not have Google).

The user can also make selections on if they want to see this information only in the New York division, or only Company Owned stores, etc. I have everything working except for the "Stores Not Installed" table which seems to be the biggest challenge here.

Thank you for your help everyone.

[Anonymous]

The bit that is most frustrating about this is that I could easily write such a thing in SQL using multiple different mechanisms.....if only I could write SQL queries to populate the table:

SELECT StoreNumber

FROM Table

WHERE StoreNumber NOT IN (

     SELECT StoreNumber

     FROM Table

     WHERE SoftwareType = 'Google')

;

The closest mechanism I have found in QlikSense which might help is the Symmetric Difference operator, which could show me essentially all of the values, except for all of the values already in the other table. But again, this only seems to work with aggregate functions.

Is this something that QlikSense is even equipped to do?

[sasiparupudi1]

You Can achieve the sql

SELECT StoreNumber

FROM Table

WHERE StoreNumber NOT IN (

     SELECT StoreNumber

     FROM Table

     WHERE SoftwareType = 'Google')

using applymaps

Map_StoreNumber:

Mapping load

SoftwareType,

StoreNumber

Resident Table;

Google_table:

Load StoreNumber

Resident Table

Where ApplyMap('Map_StoreNumber'.'Google','NA')='NA'

;

[sebastiandperei]

Hi! Obviously, anything is possible with SA.

You said:

" I need to simultaneously have one table that shows all <expression> for which A='value1', B='value2', C='value3', D='value4', and E='value5', and then another table that shows all <expression> for which A='value1', B='value2', C='value3', D='value4', and E <> 'value5'. "

For example, when you are talking about "one table that shows all <expression>", we have a table with "Customer" field dimmention, and "sum(Sales)" <expression>.

Table 1 (shows all possible values)

Dimmention: Customer

Expression: Sum(Sales)

Table 2 (shows all possible values for fields A, B, C and D, but the excluded values for E)

Dimmention: Customer

Expression: Sum({<E=-E>}Sales)

You must put {<E=-E>} in all your <expressions>, and will see both tables.

Please, take in mind that if you haven't selected one value in E field, the opposite (-E) is none available.

[sebastiandperei]

=count({<SoftwareType=, StoreNumber=E()>} distinct StoreNumber)

With this expression in a table with StoreNumber Dimmention, you will see the oposite of SoftwareType selected