topic Re: SQL script to qlikview scrip in QlikView

SQL script to qlikview scrip

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

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

Re: SQL script to qlikview scrip

zhadrakas — Fri, 26 Jan 2018 09:10:15 GMT

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

Re: SQL script to qlikview scrip

mandilicious — Fri, 26 Jan 2018 09:18:26 GMT

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

Re: SQL script to qlikview scrip

zhadrakas — Fri, 26 Jan 2018 09:24:59 GMT

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.

Re: SQL script to qlikview scrip

mandilicious — Fri, 26 Jan 2018 12:53:20 GMT

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

Re: SQL script to qlikview scrip

marcus_sommer — Fri, 26 Jan 2018 12:54:24 GMT

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

Re: SQL script to qlikview scrip

mandilicious — Wed, 31 Jan 2018 11:40:52 GMT

Hi Marcus

If i may ask then which one is correct?

Thanks

Re: SQL script to qlikview scrip

marcus_sommer — Wed, 31 Jan 2018 12:10:14 GMT

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