Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ketanvaria
Contributor III
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 (1)
5 Replies
Anonymous
Not applicable

would you be able to draw on paper a sketch of how you want your chart to looks like?
ketanvaria
Contributor III
Contributor III
Author

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

PrashantSangle

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
lironbaram
Partner - Master III
Partner - Master III

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 

Anonymous
Not applicable

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 🙂