Overlay bar chart from 2 sources

[#1]

Hi all,

I am new to Qlikview, and am trying to get my head around what I am doing here.  Any helps would really be appreciated.

I have 2 data sources, like below.

Assessed Skills
Person ID	Person RoleID	AssessedSkillID	Assessed_SkillName
1	24	7701	QlikView
1	24	7703	Excel
1	24	7704	SQL Server
2	22	7701	QlikView
2	22	7701	QlikView
3	26	7704	SQL Server
4	26	7703	Excel

Recommended Skills
RoleID	SkillID	Recommended_SkillName
22	7740	C# .NET
22	7760	Hadoop
24	7701	QlikView
24	7703	Excel
24	7706	Tableau
24	7710	SSRS
26	7703	Excel
26	7723	Word

I am trying to do the following:

1) After selecting a Role ID (it will likely be from Assessed Skill table), I'd want it to display a bar chart listing all skills from 'Recommended Skills' table for that Role ID (so [Person Role ID] = [RoleID])

2) I then want to compare how many people from that Role actually have the skill that is recommended - so I want to display a count of people who have that same skill in Assessed Skills table. [SkillID] = [Assessed_SkillID]

Take into account here that Assessed Skills table can have many many AssessedSkillID's that are unrelated to the Recommended Skills SkillID's.

e.g. if I select Role 24, I want the barchart to show me "QlickView", "Excel", "Tableau", "SSRS" as the Recommended Skills for that role -- it should then overlay the Assessed Skills that match it, so for Role 24, we have 1 person, it should show 1 for "Qlikview", 1 for "Excel" and 0 for "Tableau" and "SSRS"... for everybody in Role 24 (assume this table is bigger and there are alot more Person ID's with this RoleID

Hope thats clear.  Would appreciate any pointers.

[Gysbert_Wassenaar]

See attached example

[#3]

Thanks Gysbert,

I have looked at your example and I think I understand the following:

You are concatenating all Assessed skills into a single long string, then searching that String for the Recommended Skill for each person,and if found adding a 1 for that Person.

Firstly I have modified it slightly to look up by the Skill ID instead of the Name, since the two tables may not have an exact match on the Name, since it is a string and there may be some truncation in one but not the other.  Skill ID is the Primary key which will always match.

I have done this now:

if(Index(concat(AssessedSkillID,'|'),SkillID),1,0)

I think it is giving me the same result, but I am having difficulty displaying this on a chart, like a bar chart.  I've updated the source data a bit and have re-attached what you sent me with my updates.

If I filter on Role '26'... notice how on the two charts I added (one table, one bar).  "Excel" shows as '1' because I have removed Person ID.  I want the bar to show '3' for Excel, and '0' for 'Outlook, Sharepoint, Word, Powerpoint etc.

Can you show me how this could be done, I would want to be summing all the '1's your Expression has so I can plot the total number on the chart correctly.

Thanks for your help, appreciate it!

attached updated file.

[Gysbert_Wassenaar · Accepted solution]

Try sum(aggr(if(Index(concat(AssessedSkillID,'|'),SkillID),1,0),[Person ID], Recommended_SkillName))

[#5]

hmmm, that works fine with my dummy sample that I created here, but when I move it over to my real data it isn't working, it looks like it is but its giving me a completely inaccurate number.

I am trying to figure out why now and will post back with any findings

*edit* yeh I can't seem to get it working properly, its showing me all the Recommended Skills fine, but the count is way way off.  If I go to my table view I can see there is actually 92 people with the given selection criteria for this skill, but this is only saying 9.

I think the issue is stemming from the way Qlikview is joining the two tables.  On my Assessed Skills table I have over 1 million rows, but on the Recommended Skills I have 30k rows.  Since they are joined by the RoleId it looks like Qlikview is handling it weirdly --- I cannot recreate this with a smaller dataset, so it seems to be something funny with how QlikView handles larger data perhaps.

For example, if I go to a table box and view "Person ID", RoleID, "Assessed_SkillName", "Recommended Skill Name", "SkillID"  (take note that this is a mix of fields from 2 tables joined together in this view).

It only duplicates my Person ID a MAX of 9 times before it moves onto the next Person ID (as such it will never show me the true number).  I cannot recreate this with a small data-set it shows all on my smaller sample, so I am wondering if it is because my Assessments table has over a million rows in it that it has a cut-off for large tables so its not creating hundreds or thousands of mostly duplicate rows.

Is there another way I can achieve what I am trying to do?

[#6]

ok I think the issue in my above post was because I have a group of multiple Roles, and I wasn't necessarily looking at one single RoleID - when looking at multiple RoleID's some of which contained the same Recommended Skill ID's it was giving me bad data.

As soon as I filter it down to just a single RoleID it displays the data fine, so for this purpose I will just ensure to use only a single Role ID.

I will mark your answer as correct, but if you know of a way it could work where I have multiple RoleID's in a group without getting messed up that would be great