topic Re: Count matching characters in QlikView

Count matching characters

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

Hi all ,

Sunny Talwar

Petter Skjolden

I need to count the matching characters in a field value

example: one exam 100 questions are there I have written 100 answers like

ABCACDAABC............ like 100

for this key also avaialbe in second table like

ABBBBCCCCC so my answer and key to match and check how many correct or wrong by letter by letter

in this ABCACDAABC --my answers

| | \ \ | | | \ \ \ \ in this example - 3 is correct answer count 7 is wrong count

ABBBBCCCCC -- key so First letter A-A 1st answer correct so count 1 [so how many correct answers that count and wrong count]

B-B correct so like that correct answer count and wrong answer count

Re: Count matching characters

passionate — Sat, 14 Apr 2018 17:28:19 GMT

Here you Go.

PFA

Re: Count matching characters

soniasweety — Sat, 14 Apr 2018 18:12:58 GMT

thanks for your response I feel its correct but I need to check with data

so now am getting the sum of answerkey as 51 fro 00036199

now I want to show remaining 49 in other column so how can I get?

Re: Count matching characters

passionate — Sat, 14 Apr 2018 18:14:38 GMT

Just use 100 - sum(AnswerKey)

Re: Count matching characters

petter — Sat, 14 Apr 2018 18:42:22 GMT

For the number of correct answers:

SubstringCount(Concat(Mid(Answers,ValueLoop(1,100),1)=Mid(Correct,ValueLoop(1,100),1),'', ValueLoop(1,100)),'1')

For the number of incorrect answers:

SubstringCount(Concat(Mid(Answers,ValueLoop(1,100),1)=Mid(Correct,ValueLoop(1,100),1),'', ValueLoop(1,100)),'0')

These are expressions that must be used in a straight table and not in the load script.

Re: Count matching characters

soniasweety — Sat, 14 Apr 2018 18:44:20 GMT

tried but its giving wrong result. for 51 its showing 4 remaing

Re: Count matching characters

soniasweety — Sat, 14 Apr 2018 18:45:08 GMT

thanks petter

could you please attach the sample ?

Re: Count matching characters

petter — Sat, 14 Apr 2018 18:46:42 GMT

In a straight table. Answers and Correct are two fields containing the 100 character strings.

Re: Count matching characters

soniasweety — Sat, 14 Apr 2018 18:55:06 GMT

got it thank you which answer I need to correct you or Pankaj both help is highly appreciated

Re: Count matching characters

petter — Sat, 14 Apr 2018 19:05:15 GMT

I don't run QlikView at the moment - only Qlik Sense.

Re: Count matching characters

MarcoWedel — Sat, 14 Apr 2018 22:52:57 GMT

Hi,

if you also want to extend the granularity of your analysis to include the different questions itself, then maybe one solution can be:

tabTests:
LOAD *
FROM [https://community.qlik.com/servlet/JiveServlet/download/1471086-322045/Match.xlsx] (ooxml, embedded labels, table is Sheet1, filters(Remove(Row, Pos(Top, 10)),Remove(Row, Pos(Top, 9)),Remove(Row, Pos(Top, 8))));

tabResults:
LOAD *
FROM [https://community.qlik.com/servlet/JiveServlet/download/1471086-322045/Match.xlsx] (ooxml, embedded labels, table is Tabl1);

tabAnswersTemp:
LOAD [Test Number],
    Key
Resident tabTests;
Inner Join
LOAD [Test Number],
    SSN,
    [Answer Sheet/ Scores]
Resident tabResults;

tabAnswers:
LOAD *,
    If(Answer=CorrectAnswer,Dual('Yes',1),Dual('No',0)) as IsCorrect;
LOAD SSN,
    IterNo() as Question,
    Mid(Key,IterNo(),1) as CorrectAnswer,
    Mid([Answer Sheet/ Scores],IterNo(),1) as Answer
Resident tabAnswersTemp
While IterNo()<=Len(Key);

DROP Table tabAnswersTemp;

hope this helps

regards

Marco

Re: Count matching characters

soniasweety — Mon, 16 Apr 2018 18:26:33 GMT

in my original data it is showing duplicates how can I insert distinct in the expression?

example correct :40 means its showing 80