map and match

[soniasweety]

Hi all,

Anil Babu

Marco Wedel

Petter Skjolden

Sunny Talwar

I have a data like this

TableA:

ID,Num1,Answer

1,1A,   AAA

1,2A,FFF

2,2B,BBB

3,3C,CCC

3,4C,DDD

TableB:

ID,NumOf_IDS,Key

1,2,AAA

2,1,HHH

3,2,CCC

result:

ID,Num1, result

1,1A,PASS

1,2A,Fail

2,2B,Fail

3,3c,PASS

3,4c,Fail

here my requirments is :

I need to show  how many written correct or not ?   by using answer and key field.

[sunny_talwar · Accepted solution]

Try this may be

TableB:

LOAD * INLINE [

    D, NumOf_IDS, Key

    1, 2, AAA

    2, 1, HHH

    3, 2, CCC

];

TableA:

LOAD *,

If(Exists(Key, Answer), 'PASS', 'Fail') as Result;

LOAD * INLINE [

    ID, Num1, Answer

    1, 1A, AAA

    1, 2A, FFF

    2, 2B, BBB

    3, 3C, CCC

    3, 4C, DDD

];

[soniasweety]

thanks sunny

but I need in different way

like below

TableA:

ID,Num1,MyAnswer

1,1A,   AAA

1,2A,FFF

2,2B,BBB

3,3C,CCC

3,4C,DDD

TableB:

ID,NumOf_IDS,OriginalAnswer

1,2,ABA

2,1,AAH

3,2,CCC

result:   I need to  match fieldvalue and show the result

like below :

AAA  -ABA   so in this values   A-A-A    A-B-A   I wrote one wrong answer

for example

ID  Num1 MyAnswer  ,OriginalAnswer ,TotalCorrect/Totalwrong

1,1A,AAA,ABA,2/1   -- this is result same for all rows

[sunny_talwar]

I am heading out for home, will try to look at this if no one else is able to help

Best,

Sunny

[passionate]

PFA Solution

[soniasweety]

thanks Pankaj,

passionate

but that is my frst one--

but I need second solution which the data is attached.

I need to count the matching charaacters 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

in this   ABCACDAABC  --my answers

             | | \ \ | | | \ \ \ \

           ABBBBCCCCC      -- key       so First letter A-A   1st answer correct so count 1

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

[soniasweety]

sample

[shiveshsingh]

please find attached.

T1:

LOAD [Test Number],

    [TCO - Test Control Officer],

    [RAW Score],

    [RAW Score Percentage],

    Questions, Race,

    Gender, Key

FROM

[..\Desktop\Match.xlsx]

(ooxml, embedded labels, table is Sheet1);

left Join

T:

LOAD

SSN, [Test Number],

    [TCO - Test Control Officer],

    [RAW Score],

    [RAW Score Percentage],

    Race, Gender,

    [Answer Sheet/ Scores]

FROM

[..\Desktop\Match.xlsx]

(ooxml, embedded labels, table is Tabl1);

Final:

load *,if(Key=[Answer Sheet/ Scores],'Pass','Fail') as Result

Resident T1;

Drop table T1;