How to display results using specific conditions

[rawanh120]

Hello,

I would like to do the following, and I'm not sure if Qlik sense can do this or not
I have a data (excel sheet) that contains the following:
1- Data
2- IP address
3- User

I imported the data, and display it in a "Table".

I would like to search for a specific "user" in a specific "date", and display all other rows with the users that have the same "IP Address".

[stevejoyce]

Hi @rawanh120 

Add a filter pane object for User and Date for your search.

Add a table with your dimensions: Date, IP Address, User.  Add an expression like below, that will match ipaddresses to your filtered selections, but excluding your user selection, ignoring your.  In below scenario i am returning 1 if I meet this condition and displaying the text IP Adress Match, you can change to how you want.  Also note, update below calculations to your actual field names in the set analysis.:

=Pick(Count(Distinct {<user=E(user), ipaddress=P(ipaddress)>} 1),'IP Address Match')

[rbartley]

Hi,
Because of the way that Qlik's Associative Engine works, you will need to decouple the Date and User filters. This is how I did it:

In the load script I introduced some data and then created two more tables, with the [Filter Dates] completely decoupled from the main table and the [Filter Users] table only connected by the IP address (since we still need the connection with the IP address in the main table.

[IP Data]:
Load * Inline [
Date , User , IP Address
01/01/2021 , asdfg , 10.10.10.10
02/01/2021 , asdfg , 10.10.10.10
03/01/2021 , asdfg , 10.10.10.10
04/01/2021 , qwert , 10.10.10.11
05/01/2021 , qwert , 10.10.10.11
06/01/2021 , qwert , 10.10.10.11
07/01/2021 , qwert , 10.10.10.11
08/01/2021 , zxcvb , 10.10.10.10
09/01/2021 , zxcvb , 10.10.10.10

];


[Filter Dates]:
Load
Distinct [Date] as "Date Filter"
Resident [IP Data];

[Filter Users]:
Load
Distinct
[User] as "User Filter",
[IP Address]
Resident [IP Data];

Then in the sheet, I created a table with dimensions as follows:

Date (uncheck "Include null values"):

=if((User=GetFieldSelections([User Filter]) and Date=GetFieldSelections([Date Filter])) Or User<>GetFieldSelections([User Filter]),Date)

User:

=[User]

IP Address:

=[IP Address]

I then added a filter panel with the [Date Filter] and [User Filter] fields.

Here is the result:

For the selected user, it uses both the date and user selection, but for other users it only uses the user selection to work out which other records should be displayed.

I have attached the app.

[rawanh120]

Thank you @rbartley for the helpful answer.

I tried to perform the same steps in the sample data (attached).

1- First I choose the date (09/08/2021), nothing reflected on the table.

2- Then I choose the user, then the result shows other users that shared the same IP address with (user1).

Can I only get the same IPs but in the same date, for example: only the chosen date (09/08/2021)?

[rbartley]

Hi,

Sorry, when you wrote: "I would like to search for a specific "user" in a specific "date", and display all other rows with the users that have the same "IP Address", I didn't realise that you meant for the selected date.  In which case, just change the definition of the Date column to:

=if((User=GetFieldSelections([User Filter]) and Date=GetFieldSelections([Date Filter])) Or (User<>GetFieldSelections([User Filter]) and Date=GetFieldSelections([Date Filter])),Date)