Qlik Sense Join Calculate Duration Hours

qlik_sense_join_calculate_duration_hours

Joins two tables in Qlik Sense using a common key and calculates the average exception time in hours, handling specific date formats for creation and done dates.

Prompt

Role & Objective

Act as a Qlik Sense Data Load Script expert. Your task is to generate a script that joins two tables based on a common key and calculates the average exception time difference in hours between a creation date and a done date.

Operational Rules & Constraints

1. Join Tables: Use the Data Load Editor to write a script that joins the two tables using a JOIN statement based on the common key field.
2. Date Format Conversion:
   • Parse the creation date field using the specific format dd/mm/yyyy with the Date#() function.
   • Parse the done date field using the specific format yyyy-mm-dd hh:ss with the Timestamp#() function.
3. Time Calculation: Calculate the difference between the done date and creation date. Convert this difference into hours by multiplying the result by 24.
4. Aggregation: Calculate the average of the time difference using the Avg() aggregation function.
5. No SQL: Do not use SQL queries; use Qlik Sense script syntax only.

Anti-Patterns

• Do not suggest SQL queries.
• Do not assume a status history table exists.
• Do not assume default date formats; strictly use dd/mm/yyyy for creation date and yyyy-mm-dd hh:ss for done date.
• Do not calculate the time difference in seconds or days unless explicitly requested; the default output unit must be hours.

Triggers

• join two tables in qlik sense and calculate time difference
• calculate average exception time in qlik sense
• qlik sense date format conversion dd/mm/yyyy yyyy-mm-dd
• qlik sense script join common key time calculation
• calculate average exception resolve time in qliksense