8 Replies Latest reply: Oct 23, 2012 8:33 AM by brytesikaz RSS

    Coverting PL/SQL into qlikview syntax

      Hi Guys,

       

      Am new to qlikview and i have been working on oracle, can some one help me convert the following code to qlikview syntax.

       

       

       

       

              CASE WHEN "GENPOLICY"."POL_CANCEL_CDE" IS NULL THEN "MDRCANCELREF"."MDRCANCELREF_KEY"

                        ELSE "GENPOLICY"."POL_CANCEL_CDE"

                   END As "Cancel Code",

       

       

       

       

      FROM   "INSIGHT"."GENPOLICY" "GENPOLICY"

       

       

                  INNER JOIN "INSIGHT"."MDR_POLICY" "MDR_POLICY"

                  ON "GENPOLICY"."POLICY_KEY"="MDR_POLICY"."MDR_POLICY_KEY")

       

       

                  LEFT OUTER JOIN "INSIGHT"."MDRCANCELREF" "MDRCANCELREF"

                  ON "GENPOLICY"."POL_CANCEL_CDE"="MDRCANCELREF"."MDRCANCELREF_KEY")

        • Re: Coverting PL/SQL into qlikview syntax
          Gysbert Wassenaar

          You can use the sql statement without changes in qlikview.

           

          MyTable:

          Select * from Sometable where a=1;

           

          Qlikview just sends the sql statement to the database that you connect to. The database executes the statement and returns the results. You do first need to define a connection of course.

          • Re: Coverting PL/SQL into qlikview syntax

            I am also new to qlink but when I try to connect to sql server and run my sql query it run for me.

             

            I did not use 'load' in script. 

             

            only

             

            Connection string 

             

            SELECT *

            FROM

            WHERE

            INNER JOIN

             

            I hope this will help you to get out from your problem.

            • Re: Coverting PL/SQL into qlikview syntax
              Abhishek Pandey

              Hi brytesikaz 

               

              I have tried to convert your PL SQL in QV statements..

               

              It is not a complete script but atleast you will get an idea of how to do it with qlikview.

               

              Few point to check:

               

              1. First identify all the Raw tables from the PLSQL and store it in QVD's

               

                 e.g Seeing your PLSQl script, the table you want is 

               

                  "INSIGHT"."GENPOLICY","INSIGHT"."MDRCANCELREF","INSIGHT"."MDR_POLICY" and

                   "INSIGHT"."MDR_PRODUCT".

               

              2. Identify the joins and calulation conditions you want to apply.As in SQL in Qlikview also you can Join  tables.Take help from below blog for understanding joins in Qlikview.

               

              http://www.qlikfix.com/2011/02/07/merging-tables-the-join-prefix/

               

               

              ENPOLICY_T:

               

              LOAD

              "POL_CANCEL_CDE",

               

              "POL_CANCEL_CDE" AS Key1,

               

              "POLICY_KEY" as Key2

               

              FROM "INSIGHT"."GENPOLICY" ;

               

              LEFT JOIN

               

              LOAD

              "MDRCANCELREF_KEY",

              "MDRCANCELREF_KEY" AS Key1

              FROM "INSIGHT"."MDRCANCELREF";

               

              Drop field Key1;


              MDR_POLICY_T:

               

              LOAD


              "MDR_POLICY_KEY" as Key2,

              "MDR_PRODUCT_CDE" As Key1


              FROM "INSIGHT"."MDR_POLICY" ;


              INNER JOIN

               

              LOAD

              "MPD_UW_CDE",

              "MPD_PRODUCT_KEY" AS Key1


              FROM "INSIGHT"."MDR_PRODUCT";

               

              MDR_POLICY:

               

              Inner Join( GENPOLICY)

               

              LOAD "MPD_UW_CDE",


                        Key2
                  
              Resident MDR_POLICY_T;

               

              Drop table MDR_POLICY_T;


              GENPOLICY:

               

              LOAD *,

              IF ( "MPD_UW_CDE"=8,'Chartis Policy Cancellations', IF ( "MPD_UW_CDE"=10,'Chartis Policy Cancellations','Other Policy Cancellations')) as [Policy Cancellations]

              Resident GENPOLICY_T;

              Drop table GENPOLICY_T;

               

               

              Hope it will help you in solving your problem.

               

              Thanks