topic Re: Coverting PL/SQL into qlikview syntax in QlikView

Coverting PL/SQL into qlikview syntax

Tue, 23 Oct 2012 09:18:20 GMT

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 — Tue, 23 Oct 2012 09:27:35 GMT

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

Tue, 23 Oct 2012 09:34:14 GMT

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

Re: Coverting PL/SQL into qlikview syntax

Tue, 23 Oct 2012 09:39:01 GMT

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

Gysbert_Wassenaar — Tue, 23 Oct 2012 10:05:39 GMT

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.

Re: Coverting PL/SQL into qlikview syntax

Tue, 23 Oct 2012 10:31:26 GMT

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

Re: Coverting PL/SQL into qlikview syntax

Tue, 23 Oct 2012 10:35:01 GMT

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

Re: Coverting PL/SQL into qlikview syntax

Gysbert_Wassenaar — Tue, 23 Oct 2012 11:10:26 GMT

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

Re: Coverting PL/SQL into qlikview syntax

Tue, 23 Oct 2012 12:33:35 GMT

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