topic Re: Comparing time stamp data in QlikView

Comparing time stamp data

praveenkumar_s — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Comparing time stamp data

avkeep01 — Fri, 24 Nov 2017 15:38:12 GMT

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;

Re: Comparing time stamp data

praveenkumar_s — Fri, 24 Nov 2017 15:58:37 GMT

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

Re: Comparing time stamp data

avkeep01 — Mon, 27 Nov 2017 09:37:32 GMT

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 ;