topic Re: Linking data! in QlikView

Linking data!

Tue, 28 May 2013 16:16:21 GMT

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!

Re: Linking data!

Tue, 28 May 2013 20:24:05 GMT

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

Re: Linking data!

rustyfishbones — Tue, 28 May 2013 20:33:16 GMT

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??

Re: Linking data!

Tue, 28 May 2013 20:46:18 GMT

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!

Re: Linking data!

rustyfishbones — Tue, 28 May 2013 21:00:56 GMT

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

Re: Linking data!

Tue, 28 May 2013 21:08:13 GMT

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!

Re: Linking data!

rustyfishbones — Tue, 28 May 2013 21:31:40 GMT

Just use one table like below and reload into Qlikview

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

Re: Linking data!

Tue, 28 May 2013 21:39:52 GMT

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?

Re: Linking data!

rustyfishbones — Tue, 28 May 2013 21:53:46 GMT

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

Re: Linking data!

Tue, 28 May 2013 21:55:43 GMT

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.

Re: Linking data!

rustyfishbones — Tue, 28 May 2013 22:23:57 GMT

Try this

=IF(RANK(SUM(Spend),[Number of Vendors])<=4,SUM(Spend))/SUM(TOTAL Spend)

Re: Linking data!

Tue, 28 May 2013 23:09:02 GMT

Thanks! I ended up using your trick on data linking and the concentration ratio etc ended up working itself out when I created linked data!