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: 
mdmukramali
Specialist III
Specialist III

Help needed in String Function

Dears,

I have an exam system where 50 Questions are available.

the questions are multiple choose answers where student can select more than 1 answer.

suppose 1 student selected for QuestionA 1st answer

and 1 more student selected for QuestionA 1st&2nd Answer.

so i want to see how many students for QuestionA selected 1st Answer and how many selected 2nd Answer.

from the above example we can say 1st answer two students and 2nd answer one student.

kindly find the attached sample data for the same.,

Thanks,

Mukram.

5 Replies
m_woolf
Master II
Master II

See the attached qvw

stigchel
Partner - Master
Partner - Master

I would organize your data differently, store the answers seperately using the subfield function and the questions in a question field:

Tmp:

LOAD * Inline

[

ID , Question1, Question2, RESULT

101 , 1 , 2 , pass

102 , 1&3 , 3&2 , pass

103 , 1&2&3&4 , 5 , pass

104 , 6 , 11 , Fail

105 , 8 , 5 , Pass

106 , 9 , 3 , Fail

107 , 11 , 2 , Pass

108 , 5 , 1&4&9 , pass

109 , 3 , 1 , pass

110 , 2 , 1 & 3 , pass

111 , 1&4&9 , 1&2&3&4 , Fail

112 , 2 , 6 , Pass

113 , 3 & 2 , 8 , Fail

114 , 5 , 9 , Pass

115 , 11 , 8 , pass

116 , 5 , 9 , pass

117 , 3 , 11 , pass

118 , 2 , 5 , Fail

119 , 1&4&9 , 3 , Pass

120 , 1 , 2 , Fail

121 , 1 & 3 , 1&4&9 , Pass

122 , 1&2&3&4 , 1&4&9 , pass

123 , 6 , 11 , pass

124 , 8 , 5 , pass

]

;

Data:

CrossTable(Question,Answer,2) LOAD

ID,

RESULT,

SubField(Question1,'&') as Question1

Resident Tmp;

CrossTable(Question,Answer,2) LOAD

ID,

RESULT,

SubField(Question2,'&') as Question2

Resident Tmp;

See attached

mdmukramali
Specialist III
Specialist III
Author

Dear m w,

thanks for your reply and solution.

I have 500000 Records in my table. when i apply your solution on that data the new table exploding the data.

is there anyway without duplicating the records?

Thanks,

Mukram.

kamielrajaram
Creator III
Creator III

Hi,

Hope this helps

Regards

Kamiel

Anonymous
Not applicable

This works for your test data, but it's very narrowly tailored for your test data:

For your expression, enter this: Count(DISTINCT ID)

For your dimension: =Pick(Wildmatch(Q1,1,'1 &*','1&*',2,'*&2*','* 2*',3,'* 3','*&3*','*3 *',4,'*&4*',5,6,7,8,9,'*&9',10,11),
1,1,1,2,2,2,3,3,3,3,4,4,5,6,7,8,9,9,10,11)

If your real data have different types of formats, you're going to have to apply some rules to your data, i.e. always use a space between ampersands and numbers or never include a space. Then you can probably develop a pretty simple "Pick(Wildmatch())" formula.