Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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")
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
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.
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
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.
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.
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
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
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
Thanks alot!!! that helps alot. you guys are the best!!