Coverting PL/SQL into qlikview syntax

[#1]

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

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

[#3]

Hi Gysbert,

The thing is am trying to create QVDs so i can read data from there instead of the database for perfomance issues of course. thats the reason why i figured changing to qliview script can help

[#4]

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.

[Gysbert_Wassenaar]

You'll first need to get the data from the database to be able to store it in a qvd. You can store only one table in one qvd: store MyTable into xxx.qvd (qvd);. And getting the data from the qvd is a simple Mytable: load * from xxx.qvd (qvd); Nothing more is needed. You simply load the entire table from the qvd into qlikview.

[#6]

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

[#7]

So what you are saying is when i load the QVDs i can then do the SQL condition and join statements on the QVDs instead of the database tables

e.g

FROM   "INSIGHT"."GENPOLICY" "GENPOLICY.qvd"

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

and since Qlikview has

[Gysbert_Wassenaar · Accepted solution]

That would be something like this:

gen_mdr_policy:

load * from genpolicy.qvd (qvd);

inner join

load * from mdr_policy.qvd (qvd);

first load all fields from the genpolicy.qvd file into a table named gen_mdr_policy. Then inner join that table with all fields loaded from the mdr_policy.qvd file. The resulting table is still named gen_mdr_policy and now contains the inner join of the two datasets from the two qvd files

[#9]

Thanks alot!!! that helps alot. you guys are the best!!