Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ketanvaria
New Contributor III

I am trying to filter but by creating a dimension from inside the content of table

I have two table Table 1: List of Questions (Skill 1, Skill 2 etc)  and Choice of 4 Answers (No Knowledge, Basic, Proficient, Expert)

Table 2: A table of just the 4 Answers (No Knowledge, Basic, Proficient, Expert) for LookUp.

I want to create a simple chart with the Level aon the X-Axis (No Knowledge, Basic, Proficient, Expert) and the count by each Question (Skill 1, 2 etc) . Should be easy to do but I can't get the results despite trying many things including how the table are joined etc. 

thanks ! Ketan

Labels (2)
5 Replies
datamakery01
New Contributor III

Re: I am trying to filter but by creating a dimension from inside the content of table

would you be able to draw on paper a sketch of how you want your chart to looks like?
~ By Candice. Superb experience and enthusiasm on Tableau, Qlik Sense and Power BI. Visit https://datamakery.com for continuous learning program on analytics.
Highlighted
ketanvaria
New Contributor III

Re: I am trying to filter but by creating a dimension from inside the content of table

Of course- here is the chart done in excel after some calculations. 

Re: I am trying to filter but by creating a dimension from inside the content of table

Hi,

 

Modify your script as below

[Table 1]:
LOAD
[Skill 1] as level,
'Skill_1' as Skill

FROM [lib://AttachedFiles/Sample_Community.xlsx]
(ooxml, embedded labels, table is [Table 1]);

Concatenate
LOAD
[Skills 2] as level,
'Skill_2' as Skill

FROM [lib://AttachedFiles/Sample_Community.xlsx]
(ooxml, embedded labels, table is [Table 1]);

[Table 2]:
LOAD
[Level] as level
FROM [lib://AttachedFiles/Sample_Community.xlsx]
(ooxml, embedded labels, table is [Table 2]);

 

Then In front end use bar chart

take 

Dimension : level

Measure : 

1: Count({<Skill={'Skill_1'}>}Skill)

2: Count({<Skill={'Skill_2'}>}Skill)

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Partner
Partner

Re: I am trying to filter but by creating a dimension from inside the content of table

hi

i would have used this method 

using the cross table function of table 1 


[Table 1]:
CrossTable(Skill,Level,1)
LOAD rowno() as ID ,
[Skill 1],
[Skills 2]
FROM [lib://AttachedFiles/Sample_Community.xlsx]
(ooxml, embedded labels, table is [Table 1]);

then you'll be able to create your chart 

Level as 1st dimension 

Skill as 2nd dimension 

count(ID) as your expression 

datamakery01
New Contributor III

Re: I am trying to filter but by creating a dimension from inside the content of table

ok great. you probably don't need your second table. you just need to play with the first one. here is the generic step:
1. load your 1st table into qlik sense
2. go to data manager > table
3. click on edit (pencil icon)
4. then, find the unpivot button > click on 2 Columns > click on Apply Unpivoting.
5. Rename your first column as "Type of Skill". Rename your second column as "Skill Level"
5. click load data button
6. Edit your sheet, add in the Bar Chart
7. 1st Dimension = "Skill Level". 2nd Dimension="Type of Skill".Measure = "Count(Type of Skill)
8. Go to the Bar Chart's appearance then change the display to your needs.
done 🙂
~ By Candice. Superb experience and enthusiasm on Tableau, Qlik Sense and Power BI. Visit https://datamakery.com for continuous learning program on analytics.