Date is not working when join the two tables.

[Kohli]

I have two tables. like

Table1:                       Table2:

CanID,                         CanID

BatchID,                     BatchID

PaperID,                    Date,

Quest,                             TQ

Answ,                             TA

Marks,                             TC

Iam join the two table based on BatchID, CanID. I created the Day, Month, Years Fields based on Date from the Table2. And created filters for the same.

When i select the day or month filters. the first table is not responding. only the CanID, BacthID is showing and remaining coloums showing - symbols.

How can i link the two tables and  Table1 is respond when select the date.

[NhanNguyen]

How did you do the join? Could you provide the script that you wrote?

[silambarasanmp]

Hey, Use this code. ExamTable: LOAD canId&'-'&BatchId as CAN_KEY, paperId, Qno, answer, Marks, complexity, "section" FROM [lib://ABC/Exam Analysis.xlsx] (ooxml, embedded labels, table is Sheet2); Left Join(ExamTable) LOAD "CANDIDATE ID"&'-'&"BATCH ID" as CAN_KEY, "EXAM PAPER ID", TQ, TA, TC, MARKS, RESULTS, EXAMINATION, "DATE OF EXAM", "HALL TICKET", CCOMMENT, ECOMMENT FROM [lib://ABC/Exam Analysis.xlsx] (ooxml, embedded labels, table is Sheet1);

[Kohli]

Candidate:

LOAD

1 AS Flag,
canId AS Candidtae_ID,
paperId AS Paper_ID,
Qno AS Question_NO,
answer AS Answer,
BatchId AS Batch_ID,
Marks,
complexity AS Complexity,
"section" AS Section
// 'date' AS Exam_Date
FROM [lib://EXAM ANALYSIS1/Exam Analysis.xlsx]
(ooxml, embedded labels, table is Sheet2);

join (Candidate)

Batch:

LOAD

2 AS Flag,
"BATCH ID" AS Batch_ID ,
"CANDIDATE ID" AS Candidtae_ID,
"EXAM PAPER ID",
TQ,
TA,
TC,
MARKS,
RESULTS,
EXAMINATION,
"DATE OF EXAM" AS Exam_Date,
Date("DATE OF EXAM") AS Day,
Month("DATE OF EXAM") AS Month,
Year("DATE OF EXAM") AS Year,
"HALL TICKET" AS Hall_Ticket,
CCOMMENT,
ECOMMENT
FROM [lib://EXAM ANALYSIS1/Exam Analysis.xlsx]
(ooxml, embedded labels, table is Sheet1);

[NhanNguyen]

As I can see from there, my suggestions are:

- Don't do a JOIN. There might be some reasons behind it, but you can create 2 tables: Candidate and Batch ID, then link them with one key e.g. CandidateID. 

- remove all redundant fields in Candidate table and keep them in 'Batch' table only as you later join/link them. 

- create a MasterCalendar table to store all of your date data in which you can expand it by adding day, month, week and even year. MasterCalendar and either Batch or Candidate table can link to it by ExamDate. 

With that, your data model looks so much cleaner and avoid synthetic keys. 

[kaanerisen]

Hi Kohli,

You said you join the two table based on BatchID, CanID but, It seems you joined two tables over three field. 

BatchID,CandidateID, Flag(which is created for tagging the records I guess ). And flag values are always different for two tables. So the tables don't have related records. 

Candidate:

LOAD
1 AS Flag,
canId AS Candidtae_ID,
paperId AS Paper_ID,
Qno AS Question_NO,
answer AS Answer,
BatchId AS Batch_ID,
Marks,
complexity AS Complexity,
"section" AS Section
// 'date' AS Exam_Date
FROM [lib://EXAM ANALYSIS1/Exam Analysis.xlsx]
(ooxml, embedded labels, table is Sheet2);

join (Candidate)

Batch:

LOAD
2 AS Flag,
"BATCH ID" AS Batch_ID ,
"CANDIDATE ID" AS Candidtae_ID,
"EXAM PAPER ID",
TQ,
TA,
TC,
MARKS,
RESULTS,
EXAMINATION,
"DATE OF EXAM" AS Exam_Date,
Date("DATE OF EXAM") AS Day,
Month("DATE OF EXAM") AS Month,
Year("DATE OF EXAM") AS Year,
"HALL TICKET" AS Hall_Ticket,
CCOMMENT,
ECOMMENT
FROM [lib://EXAM ANALYSIS1/Exam Analysis.xlsx]
(ooxml, embedded labels, table is Sheet1);

---

Remove the Flag fields from the tables and run the script. It should be working.

[seanbruton]

Hi There change your script to run as follows:

Candidate:

LOAD

1 AS Flag,
canId AS Candidtae_ID,
paperId AS Paper_ID,
Qno AS Question_NO,
answer AS Answer,
BatchId AS Batch_ID,
Marks,
complexity AS Complexity,
"section" AS Section
// 'date' AS Exam_Date
FROM [lib://EXAM ANALYSIS1/Exam Analysis.xlsx]
(ooxml, embedded labels, table is Sheet2);

Batch:
LOAD

2 AS Flag,
"BATCH ID" AS Batch_ID ,
"CANDIDATE ID" AS Candidtae_ID,
"EXAM PAPER ID",
TQ,
TA,
TC,
MARKS,
RESULTS,
EXAMINATION,
"DATE OF EXAM" AS Exam_Date,
Date("DATE OF EXAM") AS Day,
Month("DATE OF EXAM") AS Month,
Year("DATE OF EXAM") AS Year,
"HALL TICKET" AS Hall_Ticket,
CCOMMENT,
ECOMMENT
FROM [lib://EXAM ANALYSIS1/Exam Analysis.xlsx]
(ooxml, embedded labels, table is Sheet1);