topic Re: Convert SQL code into QlikView Script in QlikView

Convert SQL code into QlikView Script

lawrance — Sat, 22 Apr 2017 10:40:17 GMT

Hi,

How to convert the below SQL code into QlikView Script. Kindly share the solution.

CASE

WHEN Sum(CASE

WHEN COND_IND BETWEEN 100000 AND 199999 THEN 1

ELSE 0

END)>0 THEN 'Y'

ELSE 'N'

END AS READ_YN

Sum(CASE

WHEN TO_CHAR(STRT_DTTM, 'hh24:mi:ss')='00:00:00' THEN 1

ELSE 0

END) AS MID_CNT

,Sum(CASE

WHEN TO_CHAR(STRT_DTTM, 'mi:ss') IN ('00:00', '30:00') THEN 1

ELSE 0

END) AS HALF_CNT

GROUP BY

R.MTR_KY

,TRUNC(STRT_DTTM)

,SECONDS_PER_INTVL_CNT

,CASE

WHEN CHNL_VBSE_DESC='ERROR' AND

CHNL_DESC_2=101 THEN 'REGISTER'

ELSE CHNL_VBSE_DESC

END

,CHNL_DESC_2

,MSMT_COND_IND;

Thanks,

Lawrance A

Re: Convert SQL code into QlikView Script

vinieme12 — Sat, 22 Apr 2017 14:14:35 GMT

sum( if(COND_IND >=100000 AND COND_IND <= 199999 ,1,0)) AS READ_YN

if(floor(time(STRT_DTTM))=0 ,1,0) AS MID_CNT

if(Match(time(time#(STRT_DTTM, 'mm:ss') ,'mm:ss') ,'00:00', '30:00')>0 ,1,0) AS HALF_CNT

replace WHEN THEN ELSE with if( <condition> , <then> , <else> )

Re: Convert SQL code into QlikView Script

lawrance — Sat, 22 Apr 2017 16:18:21 GMT

Hi all,

Kindly, find the below updated code. Please help me to convert the below SQL code to QlikView Script.

SQL SELECT

KEY,

TRUNC(STRT_DTTM) AS STRT_DT,

SECONDS_CNT,

CASE

WHEN Sum(CASE

WHEN COND_IND BETWEEN 100000 AND 199999 THEN 1

ELSE 0

END)>0 THEN 'Y'

ELSE 'N'

END AS READ_YN,

Sum(CASE

WHEN COND_IND BETWEEN 100000 AND 199999 THEN 1

ELSE 0

END) AS READ_CNT,

Sum(CASE

WHEN TO_CHAR(STRT_DTTM, 'hh24:mi:ss')='00:00:00' THEN 1

ELSE 0

END) AS MIDNIGHT_CNT

,Sum(CASE

WHEN TO_CHAR(STRT_DTTM, 'mi:ss') IN ('00:00', '30:00') THEN 1

ELSE 0

END) AS HALF_CNT

,Count(KEY) AS RAW_CNT

,Sum(VAL) AS VAL

FROM RDG

GROUP BY

KEY,

TRUNC(STRT_DTTM),

SECONDS_CNT,

CASE

WHEN VBSE_DESC='ERROR' AND

CHNL_DESC_2=101 THEN 'REGISTER'

ELSE CHNL_VBSE_DESC

END

;

Regards,

Lawrance A

Re: Convert SQL code into QlikView Script

Kushal_Chawda — Sun, 23 Apr 2017 08:07:08 GMT

may be like below

Option 1: You can directly run this query from QlikView and store it in QVD

Option 2:

First create the QVD of table RDG from source like below

RDG:

SQL SELECT *

FROM RDG;

STORE RDG into Path\RDG.qvd;

Drop table RDG;

Data:

LOAD KEY,

date(floor(STRT_DTTM)) AS STRT_DT,

SECONDS_CNT,

if(COND_IND>=100000 and COND_IND<=199999 ,1,0) AS READ_CNT,

if(time(STRT_DTTM,'hh:mm:ss')='00:00:00',1,0) AS MIDNIGHT_CNT,

if(match(time(STRT_DTTM,'mm:ss'),'00:00','30:00'),1,0) AS HALF_CNT,

VAL,

if(VBSE_DESC='ERROR' AND CHNL_DESC_2=101 ,'REGISTER',CHNL_VBSE_DESC) as CHNL_VBSE_DESC

FROM RDG;

Final:

noconcatenate

LOAD KEY,

STRT_DT,

SECONDS_CNT,

Sum(READ_CNT) as READ_CNT,

Sum(MIDNIGHT_CNT) as MIDNIGHT_CNT,

Sum(HALF_CNT) as HALF_CNT,

Count(KEY) as RAW_CNT,

Sum(VAL) as VAL

Resident Data

Group By

KEY,

STRT_DT,

SECONDS_CNT,

CHNL_VBSE_DESC;

Drop table Data;

Re: Convert SQL code into QlikView Script

Peter_Cammaert — Sun, 23 Apr 2017 15:33:59 GMT

It's always preferable to use the underlying business logic for creating correct QlikView script code than trying to translate a SQL example. SQL has other performance drivers...

Especially when there is just the SQL example and no word of exaplanation...