Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
praveenkumar_s
Creator II
Creator II

Comparing time stamp data

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

3 Replies
avkeep01
Partner - Specialist
Partner - Specialist

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;

praveenkumar_s
Creator II
Creator II
Author

i need a clarification -   why you put static value 2016 in where clause?. i want to search for all the student id.

avkeep01
Partner - Specialist
Partner - Specialist

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 ;