Linking data!

[#1]

Hi,

I'm struggling with a data linking problem and could use some pointers. I have two scatterplots:

Scatterplot 1 (High Level Category)                    Scatterplot 2 (Sub-Category)

Marketing                                                              Commercial Print                               

IT                                                                         Software Applications

Infrastructure                                                         Building Expenses

Note that the sub-categories are sub-groups of the high level categories. The x and y axes are the same.

I have gotten to the point where if I filter by the sub-category, only the applicable high level category appears in the first chart. i.e, if I choose Software Applications as the filter, the second chart shows Software Applications and the first chart only shows IT. However, I also want the reverse to work. Therefore, when I filter by the high level category, ex: Marketing, that only the Commercial Print data point shows up on the second scatterplot. If all filters are clear, then all sub-categories should show up.

Any ideas?! I've been reading about data linking and concatenate functions but I'm not sure if/how they apply here.

Thanks so much!

[#2]

Hi everyone,

I realize my question above is abstract and wanted to clarify. I have attached both an excel and a qv file to this post. The goal is when someone selects Marketing in the first graph, only Commercial Print and Digital show in the second graph.

I would really appreciate any insights on how to link the data to make that happen!!!

Thanks,
Lavanya

[rustyfishbones]

You need to have a common field between each set of data

LOAD them seperately and create Key Link like the [Category L1] field

Is this just sample data for a much bigger project??

[#4]

yes, it's sample data for a huge cube.

I'm not sure what create a key link means would you mind explaining further?

Thank you Alan!

[rustyfishbones]

Hi

If you are trying to link Marketing in the first graph, so that  Commercial Print and Digital show in the second graph.

these need to be linked somehow

Will you always be loading the data in that format, you will need to clean the data before you take into Qlikview to avoid future problems

So you need to add the Category  to the Sub Category table, you could create a Mapping Table showing all Categories and their Subcategories, their are a number of ways to do it, you could manually do this in Excel bt way of VLOOKUP, but it's up to you what way you want it done

[#6]

I actually have it logically mapped out in my full Excel sheet but yes, a lookup would be easy. The problem is, when I add a column in front of sub-category that is called Category L1, it does not solve the issue in qv.

Any ideas why?

Thank you again!

[rustyfishbones · Accepted solution]

Just use one table like below and reload into Qlikview

Here is the file, If this is what you need, divvy out the points:-)

[#8]

It works for the x axis and the z axis but the concentration ratio is not additive.. a concentration ratio would tell you what % the top 4 within a category or sub category contribute to the total

so close though!!!!! is there a way to tweak the way you inputted it to have a unique concentration ratio? for instance, input the way you have and then use a diff table with concentration ratios alone for the y axis?

[rustyfishbones]

I am not sure what you mean by Concentration Ratio,do you have an example?

[#10]

In a larger data set, each sub category has several vendors. For instance, there are 5 vendors that provide marketing services. The concentration ratio would be the sum of the top 4 marketing vendors divided by total marketing vendor spend.