Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
lawrance
Creator II
Creator II

Convert SQL code into QlikView Script

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

4 Replies
vinieme12
Champion II
Champion II

             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
Creator II
Creator II
Author

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

Kush
MVP
MVP

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