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: 
mandilicious
Creator II
Creator II

SQL script to qlikview scrip

Hi Champs

I  have below SQL script that i am straggling to convert to qlikview scrip as (Average Handling Time (AHT))

SELECT

  (SUM([talk_sec])+ SUM([dispo_sec])) / COUNT(Phonenumber) AS AHT

  FROM [Campaign_].[dbo].[vicidial_agent_log]

  WHERE [lead_id] is not null AND [talk_sec] >0

 

Please correct me cause below it is pulling incorrect data.

=(Time(avg(dispo_sec + talk_sec)
/
count(phone_number),'hh:mm'))

Thanks

1 Solution

Accepted Solutions
zhadrakas
Specialist II
Specialist II

try this in script

Time(sum(dispo_sec + talk_sec)/count(phone_number),'hh:mm')) as AHT

WHERE len(trim([lead_id]))>0 and [talk_sec] >0

View solution in original post

7 Replies
zhadrakas
Specialist II
Specialist II

try this in script

Time(sum(dispo_sec + talk_sec)/count(phone_number),'hh:mm')) as AHT

WHERE len(trim([lead_id]))>0 and [talk_sec] >0

mandilicious
Creator II
Creator II
Author

Hi Tim

Thanks i have few  questions

1) Is my syntax correct from SQL to Qlikview?

2)Will be fine even if i haven't used  WHERE [lead_id] is not null AND [talk_sec] >0?

Thanks

zhadrakas
Specialist II
Specialist II

1) snytax is ok but you caluclated the average and then you dont Need to dvide that with you Count(phone_number) anymore

2) it will work even without that where Statement. Note that NULL values doesnt matter for average values. But 0 values will affect your average values.

mandilicious
Creator II
Creator II
Author

Hi Tim

Please correct me.

log_Today:
LOAD "agent_log_id",
"user",
"server_ip",
"event_time",
"lead_id",
"campaign_id",
"pause_epoch",
phone_number,
"pause_sec",
"wait_epoch",
sum(dispo_sec + talk_sec) as AHT,
Time(sum(dispo_sec + talk_sec)/count(phone_number),'hh:mm') as AHT,
"wait_sec",
"talk_epoch",
"talk_sec",
"dispo_epoch",
"dispo_sec",
status,
"user_group",
comments,
"sub_status",
"dead_epoch",
"dead_sec",
processed,
uniqueid,
"pause_type";
SQL SELECT *
FROM
"Campaign".dbo."_log_Today"

WHERE len(trim(
[lead_id]))>0 and [talk_sec] >0

marcus_sommer

It must not be a problem here but:

sum(dispo_sec + talk_sec)

isn't mandatory the same like:

SUM([talk_sec])+ SUM([dispo_sec])

and if it had correctly worked in SQL I wouldn't change it in QlikView.

- Marcus

mandilicious
Creator II
Creator II
Author

Hi Marcus

If i may ask then which one is correct?

Thanks

marcus_sommer

It depends on the context which of the versions is more preferable.

If one of the parts of a normal addition isn't a valid number else a string or NULL the result of the calculation will be NULL. In opposite to this a function like sum() will ignore each non-numeric value. Here a simplified example to demonstrate what happens:

load sum(a + b) as F1, sum(a) + sum(b) as F2 inline [

a, b

10, 5

20, x

5, 8

];

- Marcus