Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Frnds,
Please share your knowledge
I have two table.
table 1 consists of student id and "timestamp".
table 2 consists of list of "timestamp" and respective "tid".
I want to create table in load script by adding new flag column which check student id is present in previous year. if student id is present in previous year , flag is 0, else 1.
Attached excel for your reference
Hi Praveen,
You could use the following script:
table1:
LOAD * INLINE [
Student_Id, Timestamp
111, 2016
112, 2016
113, 2015
114, 2015
111, 2015
112, 2015
111, 2014
];
// Load the predefined year
table2:
NOCONCATENATE LOAD
Student_Id AS Student_Id_Exists
RESIDENT table1
WHERE Timestamp = 2016;
// add the students from earlier years <> the predefined year
LEFT JOIN (table2) LOAD
Student_Id AS Student_Id_Exists,
Student_Id,
COUNT(Timestamp) AS [# Timestamps],
1 AS Flag
RESIDENT table1
GROUP BY Student_Id;
// Add the students that didn't exists before predefined year
CONCATENATE (table2) LOAD
Student_Id,
0 AS Flag
RESIDENT table1
WHERE NOT EXISTS('Student_Id_Exists',Student_Id);
// drop temp field
DROP FIELD Student_Id_Exists;
DROP TABLE table1;
i need a clarification - why you put static value 2016 in where clause?. i want to search for all the student id.
Hi Praveen, I asumed that 2016 was a given paramater. You can make it dynamic if you want like YEAR(TODAY()). I needed to create a base table. Like a starting point to connect the rest of the years. Another solution is creating a flag for the highest year per student and use that in the where statement for the base table.
LEFT JOIN (table1) LOAD
Student_Id,
MAX(Timestamp) AS Timestamp,
1 AS HighestTimestamp
RESIDENT table1
GROUP BY Student_id;
table2: ..... WHERE HighestTimestamp =1 ;