Convert SQL code into QlikView Script

[lawrance]

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

[vinieme12]

             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> )

[lawrance]

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

[Kushal_Chawda]

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;

[Peter_Cammaert]

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...