topic Re: SQL to qlikView charts in QlikView

SQL to qlikView charts

Wed, 15 Jul 2015 09:20:58 GMT

Hello

I have an excel books with a lot of SQL from Microsoft ODBC Query File. I won't to convert those Queries to QVD /QV table and link to to master tables.... .

Is is any other tips/way to achieve such

SELECT

d.duration AS duration,

Call_day_duration.a_day ||' '||MAX(d.a_time) - '00:00:30'::TIME AS PCall_submit_trunc,

Call_day_duration.NB_Call

FROM

RC_DAYS_TIME d

INNER JOIN (

SELECT

PCall_submit::DATE AS A_DAY,

(EXTRACT(HOUR from PCall_submit)*3600+extract(MINUTE from PCall_submit)*60+extract(SECOND from PCall_submit)) AS duration,

COUNT(*) AS NB_Call

FROM

Call_CDR

WHERE

PCall_submit::DATE >= '2015-06-24'

and

PCall_submit::DATE <= '2015-07-23'

GROUP BY 1, 2

ORDER BY 1, 2

) AS Call_day_duration

ON d.duration = Call_day_duration.duration

GROUP BY

1,Call_day_duration.a_day ,3

ORDER BY 2 ;

I want a chart (bar) for PCall_submit_trunc as dim and Call_day_duration.NB_Call and expression

Thank you !

Sire

Re: SQL to qlikView charts

sinanozdemir — Wed, 15 Jul 2015 16:15:30 GMT

If you want to bring these to QlikView or QlikSense, you just need to use the following:

Table1:

LOAD

SQL SELECT

d.duration AS duration,

Call_day_duration.a_day ||' '||MAX(d.a_time) - '00:00:30'::TIME AS PCall_submit_trunc,

Call_day_duration.NB_Call

FROM

RC_DAYS_TIME d

INNER JOIN (

SELECT

PCall_submit::DATE AS A_DAY,

(EXTRACT(HOUR from PCall_submit)*3600+extract(MINUTE from PCall_submit)*60+extract(SECOND from PCall_submit)) AS duration,

And the same for other SQL statement:

Table2:
LOAD

SQL SELECT

COUNT(*) AS NB_Call

FROM

Call_CDR

WHERE

PCall_submit::DATE >= '2015-06-24'

and

PCall_submit::DATE <= '2015-07-23'

GROUP BY 1, 2

ORDER BY 1, 2

) AS Call_day_duration

ON d.duration = Call_day_duration.duration

GROUP BY

1,Call_day_duration.a_day ,3

ORDER BY 2 ;

As a result of this Table1 and Table2 will be joined with an outer join via NB_Call dimension.

Re: SQL to qlikView charts

maxgro — Wed, 15 Jul 2015 16:22:13 GMT

if I understand

define the odbc connection to your db

add the sql (in your post) to qlikview script

table:

sql

select ........ // replace with all your sql

;

and reload