9 Replies Latest reply: Aug 10, 2017 6:15 AM by kushal chawda RSS

    Scripting in Qlik

    Darmawan Suria

      Hi,

       

      Anybody can help me, how to convert this oracle Script into Qlik ?

       

      select t.app_id, t.date_create1, t.date_create2,

             (select count(date_key)

              from date_master

              where dt between trunc(t.date_create1) and trunc(t.date_create2)  and holiday_flg='N' and is_weekday = 1)  D_ANALYST,

      from Table_ALL t;

       

      Thanks

       

      Darmawan

        • Re: Scripting in Qlik
          Anil Babu Samineni

          Try this for a while?

           

          Qualify date_create1, date_create2, app_id;

          t:

          Load date_create1, date_create2, app_id From Table_Fact;

          Left Join (t)

          Load t.app_id,

                    t.date_create1,

                    t.date_create2,

                    date_key

          FROM Table_All;


          Left Join(t)

          Final_Fact:

          NoConcatenate

          Load *, Count(date_key) as D_Analyst, 1 as Flag from date_master where (dt >= TimeStamp(t.date_create1) or dt <= TimeStamp(t.date_create2)) or holiday_flg = 'N' or is_weekday = 1 Group By

          t.app_id, t.date_create1, t.date_create2, date_key;

          Drop Table t;

          • Re: Scripting in Qlik
            Jonathan Dienst

            In general, it is not necessary to convert the SQL statement to QV, you can execute it in the DBMS (Oracle) using the SQL keyword, and this will often perform better than doing the logic in QV:

             

            Table_ALL:

            SQL

            select t.app_id, t.date_create1, t.date_create2,

                   (select count(date_key)

                    from date_master

                    where dt between trunc(t.date_create1) and trunc(t.date_create2)  and holiday_flg='N' and is_weekday = 1)  D_ANALYST,

            from Table_ALL t;

             

            Everything from SQL to the closing semi-colon is passed verbatim to Oracle and executed there.

             

            ...Unless you have a specific reason for needing to move this out of Oracle.