
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
map and match
Hi all,
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.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am heading out for home, will try to look at this if no one else is able to help
Best,
Sunny


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
PFA Solution

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thanks Pankaj,
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
sample

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
