Filtering Based on Similar Joins

#1

Hi,

I’m looking at web-based data based on countries.

There’s two types of data I’m looking at: User Country & Page Countries.

Currently on my source data, there are columns that contain a corresponding country code for the User Country & Page Country (i.e. if a user from the United Kingdom is visiting a page in the United States, their respecting User Country & Page Country codes would be UK & US).

This is difficult for the user of my dashboard, as they would rather see the actual country names instead of the codes, so I created a spreadsheet that has the Country codes assigned to the country name, and am trying to join.

The column for the Country spreadsheet with the Country name is simply called “country name.”

When I do a join with the spreadsheet on the names, I’m able to see the Country name come in the filter dropdown, however, when connecting filters with my metric sets, I am unable to have a dropdown that is separate for user/page country.

I tried to combat this by creating 2 spreadsheets, one for user & one for page, but I’m still having the same issue on the dashboard, i.e. when connecting filters, the User Country filter will correspond to Page Country filter on the metric set, after having the Page Country filter connect to the Page country name.

Looking for help on this…

#2

First let me see if I have the issue right, you have two columns in the data result
User_Country and Page_Country. These both have Country Codes not the full names.
Now you have made a new source that has data for the Country code to its name.
You joined it once and see one column in your result of Country Name.

You do not need two spreed sheets.

All you need to do on the first join is rename the column to what you joined it to.
lets say on the first join you joined User_Country (the code) to the sheet to get the User_Country_Name so you should call it that.
Then join to the sheet by the Page_Country (code) to the sheet to have the Page_Country_Name and call it that.
In the end you should have four columns:
User_Country
Page_Country
User_Country_Name
Page_Country_Name

What it seams like to have done to me is only joined it once (hopefully in that one joined only said join on one column) so that why you are only getting one column for Country name. Join it two Separate time one for each Column you want to change code to name.

#3

Hi James,

You have the context correct, and it worked to an extent after doing 2 difference joins, but my column names are still a bit wonky. Attached are some screenshots…

I renamed the outputs on both joins, Page Country being the first, and User country being the second, but I was receiving 2 Page country column titles.

If you can see below, I’m joining on “2-Digit” from the country data source.

29%20AM
Screen Shot 2019-07-17 at 11.46.29 AM.png1618×412 23.4 KB

37%20AM
Screen Shot 2019-07-17 at 11.46.37 AM.png2006×982 52.1 KB

#4

I see that you named the Joins, but that does not name the column inside the join.
Take a look inside the User Country Name and see what the column name is I bet it is Page Country Name.
change it in there and take a look at results again.

to do that, right click and configure it and at the bottom “Edit Output Elements”

#5

Hi James,

Thanks, your suggestion ended up helping the data preview names, but I’m still having an issue assigning the filters to the metric sets/slicers.

09%20PM
Screen Shot 2019-07-17 at 12.40.09 PM.png1470×280 16.5 KB

As you can see, Page Country & User Country have been added to the Metric Set Slicers…

22%20PM

But when I create 2 separate filters for Page Country & User Country, Dundas is only allowing me to pick one of the two, not mutually exclusive, meaning that when I’m setting up my Page Country filter, and I select “Connect Filters” and pick page country for a metric set, when I go to set up the User Country filter, Page Country is already selected, and when I change to User country, the Page Country filter is switched to User country, and vice versa.

Do you know of a way to fix this in the filters?

#6

@jeff need help at this point
Mario,
I have not run into a use case like this and am not sure it would be possible they way you want it to be.

From what I seeing you will need two filters.
One for Page Country and one for User Country.
If you are wanting to solve a use case of “I want both filters set to Greece, and not have to set both filters”, my first question would be are you sure you always want that to happen or just as a possibility sometimes.

In either case I think you will need to do some JavaScript here to have the one filter get set to the value of the other. (Jeff this is where I need your bag of tricks).
Until Jeff pulls a rabbit from his magic bag look at these for inspiration:
https://www.dundas.com/Support/developer/script-library/filter-and-parameter/
https://www.dundas.com/Support/learning/documentation/create-view-content/how-to/set-filter-value-by-script

My thoughts would be to have a button that reads the current value of one filter and then sets the other filter to that. I think you would need two buttons, one that sets User from page, and one that sets page from User.
Jeff is it possible to use the filter click event here instead of JavaScript?

#7

Yes, the issue here that I’m trying to solve is that User Country does not necessarily always = Page Country. So by filtering, you can see what User/Page countries result in different results for pages. I don’t have much experience in javascript, so some basic code would definitely help. Thanks for your help so far.

#8

Sounds like you want to have a list where only the rows that have a non matched user and page country.

in that case I would make a Calculated Element (btw you can make many of those in one, the only time you need to add multiple is if you are referencing something created in the Calculated Element, I mention this because I see you have three).
This element would be true if the two match or false if they do not (or the other way around) either way you can set up a filter on that to show only matches or show only non matches.

#9

Actually would want the option to have both… for example, the User Country filtered could be United States, but the Page Country can be all countries.

Again, for some reason the filtering isn’t letting me have filters for both User Country & Page Country, for some reason the slicers aren’t allowing me to connect.

#10

I do not think you want to solve that use case with filtering on the two countries (page and user).

Make a new Calculated Element that sets True when they match and false when they do not.

Then you can set a filter on that new column to show ones only ones that match or only ones that do not. Make sure you make it so you can show both (both true and false).

#11

@mario.nicastro Can you try duplicating the Data Cube you use and on the metric set, use User Country from one of the data cubes and Page Country from the other data cube. All the values should align properly since the values are the same but it may let you set up one filter for User Country and a second filter for Page Country.

#12

For an unmatched join, i’d be trying to create a list of problematic fields so that you can see the degree of your problem. 5 fields? 50? 500!???

You can create a SQL query using an idea like this to see which fields are not matching. Something like this:
https://www.codeproject.com/Questions/416599/How-to-find-the-unmatched-rows-from-two-tables

Once you know the degree of your problem, you can make a lookup table to join against to translate these abbreviated names into full country names.

I can see that others suggested a calculated element transform (and it will certainly work) but depending on how many of these operations need to be processed, you might want to do it on the database instead for better performance. It really depends on what you have…

#13

Hey Jeff

Working with just a couple Excel sheets right now… I’ll look more into the Calculated Element solution now, but if that doesn’t work, how else should I proceed?

Essentially these filters should act independtly, and not affect the other. The opposite is happening, and the metric sets attached to these are allowing me to only connect to one of them.

20%20PM

#14

I am not sure what you mean by the opposite is happening.
When you filter Page country to Canada (yes reference for Dundas) that should only filter the list to rows that have Canada and not filter anything by the User Country. If it is then there an issue in how those rows are being created.

Can you explain a little more or give example please

#15

Hi Mario,

You really need to combine these or else it’s just going to be a mess. The problem is that filters are always AND which just makes this confusing for the user if you are giving them two filters to make up for a data issue. You either need to fix these manually in your excel files or go with the calculated elements. Even with excel, you can create a data cube and join these files together with a calculated element to correct known differences in the data.

basically:
if ($Page Country$ == “Can”)
{
return “Canada”;
}

#16

Mario,
What Jeff said was what I was think might have to happen.

#17

Hi Jeff, and thanks again James. Below are some screenshots of following your advice, but not having the intended effect (or what I thought the intended effect to be) in the data preview.

38%20PM
Screen Shot 2019-08-05 at 4.49.38 PM.png1362×1018 57.9 KB

03%20PM
Screen Shot 2019-08-05 at 4.50.03 PM.png2078×1190 229 KB

#18

Hi @mario.nicastro,

It seems you might be looking at the incorrect column as the calculated element generates a new column for you. I can see from your screenshot that you named the new one ‘Page Country Names’ but your screenshot is showing ‘Country’. Also, be careful of your Data Type as you have it set to INT32 meaning it’s expecting a number to be returned. Use ‘String’.

#19

Hi Jeff,

I added the Page Country Names & User Country Names to the Calculated Elements (Screenshot of data preview below).

59%20PM
Screen Shot 2019-08-07 at 2.24.59 PM.png640×502 15.5 KB

However, I’m still having the same issues of adding both as separate filters on the dashboard.

I’ve added Page Country Names & User Country Names as Slicers to the metric sets I’ve been working with, first connecting the Page Country filter.

When I go to connect the User Country filter, this is what’s coming up:
35%20PM
The filter is already preloaded with Page Country names, and Dundas is not letting me make them mutually exclusive.

#20

Hi Mario,

With the calculated element solution that we proposed, the idea is that you should be combining into a single column and only using the new column going forward. Once you’ve taken the time to nicely clean your data into a single column/field use only the new field every time on your dashboard. You should probably even go as far as hiding the other one with data issues to avoid confusion. With a single field, you won’t have these problems of double filtering.