[Solved] Filtering datasets: one merged master file or several files?

[cedfet]

Dear everyone,

I have been using Qlik for the last few weeks to create a dashboard showcasing the size and diversity of our community across several countries.

To that extent, I have compiled and consolidated 3 Excel files representing 3 types of stakeholders, let's call them: Partners, Friends, Prizes.

Each of those files have a certain amount of similar columns, such as Town, Country, Name, Description, Image url. And a bunch of others columns that are different.

Now, here is the final product that I am looking for:

- One dashboard with a map and a point layer coloured by the 3 types of stakeholders.

- three dashboards for each of the stakeholders which are providing a bit more detailed data: Activity, etc.

Challenge
Warning: I don't understand expressions. I'm used to Excel so I can get hints & understand the logic but I have mainly used the visual tools so far and the sidebar properties. Also I don't have the vocabulary: I have difficulties to understand the differences between delimiters, parameters,

At the moment, I am struggling to define the overarching structure. I tried several ways:

- I import the three Excels, they stay separated: I cannot find a way in the Dimension>Field of a Chart to say "show me at a town level on the map the count of all the stakeholders from Excels 1,2,3".

- So I tried to concatenate the files together in the Data Manager. In the end I had one Merged file (one big circle on the Manager). Then, I created a map where I wanted to see only the Partners and not the other ones. I didn't find a way to "filter out" the rest of the data because in the Expressions popup, the "Filter by table" was showing only one table. For example, I made a count on a Bar Chart and got the results from the 3 combined files.

PS: you may say already, why are you not connecting the files together? Well, I tried but every time Qlik was changing the names of the categories: from "Country" to "Partners.Country" and I struggled for a while to find how to remove that. I went in the Data Load Editor and removed the part "AS [Partners.Country]".

- Last try, after correcting that, I came back to three different Excel files and finally were able to to have those Keys between the different categories from the 3 Excels. In the data model viewer, it shows as three tables, plus one "$Syn 3 Table". I thought it would be working because in the Expression popup, I can now select the table that I want. Unfortunately, I must be missing something because when I click Insert, it just put the text Country.

Okay, that was to show a bit my trial&error process. I struggled to really find answers in the forum and on Youtube because I feel there is a lot of redundancy in the vocabulary which makes it hard to search by keyword, i.e. "filter out", "merge", "dataset", etc could mean a lot of things based on who is asking the question.

Question

First, I hope that my text was clear. My question is to know: "is it better to have one master file where we can filter out some values in the charts; or is it better to have three Excel files where we can merge them on some charts?"

Knowing that I need one dashboard with all the data in one chart; and three dashboards with only few of the data per chart.

If you have an opinion on that and if you can put me on the right track, it would be great.

PS: the image is to show what type of dashboard I am working on. This dashboard is functioning but it's only one Excel file (Prizes) and the colours on the map are another value (winners, finalists, etc).

Thank you very much,

Cédric

[ckarras22]

Hello,

According to your description (3 consolidated files with certain amount of similar columns...), your first choice to concatenate them in one "fact" table is the best one .

Nevertheless, you need to create a column in each of the excel files (before you import them), named for example [Table Type] with values Partners, Friends, Prizes respectively. Then bring this in your dashboard as a filter.

Apart from filtering, you can use it in set analysis as well:

sum({$<[Table Type]={'Partners'}>}your_metric_here)

[cedfet]

Thanks, I actually had this Table Type in every Excel; I thought it could be useful indeed but I didn't manage to "bring it in my dashboard as a filter" to quote you. Where should I go to filter my data so I only show one type in my chart?

I will start to play with your formula a bit and see where it leads. Thank you

EDIT: So I went back to the setting of having 3 files that I will concatenate. Do you think that I should rather do a "Outer join" or a "concatenate"?

EDIT 1: Okay that's where I fall short I think, where am I suppose to enter this formula? What is a set analysis? At the moment, I put it in "Dimensions>Bars>Field".

[cedfet]

Dear @ckarras22 , dear Community,

I am coming back to my previous post.

Unfortunately, I haven't found/understood how to filter out my data from the chart.

Could you rephrase your explanation or do you have an other way?

Thank you,
Cédric

[ckarras22]

Hello

EDIT: For sure concatenation

EDIT 1: Please see some of Manish's videos on set analysis (in his channel he has more on the subject)

https://www.youtube.com/watch?v=Zpt-RwIWnq0

Also download the cheatsheet PDF

https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&ved=2ahUKEwj3xK658tz7AhUM-YUKHZzLArcQ...

[cedfet]

Super, thank you, I managed to do it.

Some tips for new learners:

1. I got confused for quite some time on filtering my table because I was putting the formula in a "Dimension" field rather than a "Measure" field.

2. In the end, I used Master Items for every Charts with this formula:

=if(match([Title_name],'Field_to_keep'),[Field_to_show],null())
Example:
=if(match([Type],'Friends'),[Town],null())

3. It also took me some time to realise that [Field to Show] could be any other data, such as "showing filtered [Town] for a Map Chart".

Have a good day