topic Re: How to create QVD from Pl/SQL ? in QlikView

How to create QVD from Pl/SQL ?

Sun, 23 Mar 2014 14:45:36 GMT

Need Qlikview Script instead of Pl/sql Script..

Re: How to create QVD from Pl/SQL ?

maxgro — Sun, 23 Mar 2014 15:05:44 GMT

if you post your oracle pl/sql script or you can explain us the logic perhaps the community can helps you

Re: How to create QVD from Pl/SQL ?

SunilChauhan — Sun, 23 Mar 2014 15:15:52 GMT

Go to Edit script( Control+e)

data Tab( bottom left)

select OLEdb-. select provider

Click next provide Credentials i.e server ip addess or Server name

provide password if any.

then finish

Click on select which is below connect In data Tab( bottom left)

select table and finish

Now you will have code like below

OLEDVB connect to..........................................

tab1 :// Give name of your choice

Select *

Load

from dbo.table name;

Now store it using below code

Store tab1 into Yourpath\tab1.qvd

hope this helps

Re: How to create QVD from Pl/SQL ?

Sun, 23 Mar 2014 15:16:25 GMT

I have written here Else part...try to convert into qvscript..

Else

{

SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER

FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

AND UPPER(prm.PRODUCT_ATTR_SEG)= :g_cProductAttrSeg

AND prm.PRODUCT_ID= :g_iProd_id -- product_id

AND NVL(prm.CHARGE_TYPE,'P')= DECODE(:g_iCharge_type, 1, 'I', 2,'P','P') ---- G_ICHARGE_TYPE 1 OR 2 CAN BE FOUND IN BILL PRODUCT CHARGE

AND NVL(prm.CUSTOMER_TYPE_ID,:g_cCustflag) = :g_cCustflag;

IF(NO_DATA_FOUND)

{ SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER

INTO :g_iGsiRev_Id,:lProductCode,:g_cProfCen,:lDunning_area_desc, :g_cCost_Centre

FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

AND UPPER(prm.PRODUCT_ATTR_SEG)=:g_cProductAttrSeg

AND NVL(prm.CUSTOMER_TYPE_ID,:g_cCustflag) = :g_cCustflag

AND prm.SERVICE_TYPE=:gServType ----service type

AND prm.PRODUCT_ID IS NULL

AND prm.PRODUCT_FAMILY_ID IS NULL;

}

Re: How to create QVD from Pl/SQL ?

SunilChauhan — Sun, 23 Mar 2014 15:25:01 GMT

Create Qvd Seperately using below

Tab1:

SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER

FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

AND UPPER(prm.PRODUCT_ATTR_SEG)= :g_cProductAttrSeg

AND prm.PRODUCT_ID= :g_iProd_id -- product_id

AND NVL(prm.CHARGE_TYPE,'P')= DECODE(:g_iCharge_type, 1, 'I', 2,'P','P') ---- G_ICHARGE_TYPE 1 OR 2 CAN BE FOUND IN BILL PRODUCT CHARGE

AND NVL(prm.CUSTOMER_TYPE_ID,:g_cCustflag) = :g_cCustflag;

IF(NO_DATA_FOUND)

{ SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER

INTO :g_iGsiRev_Id,:lProductCode,:g_cProfCen,:lDunning_area_desc, :g_cCost_Centre

FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

AND UPPER(prm.PRODUCT_ATTR_SEG)=:g_cProductAttrSeg

AND NVL(prm.CUSTOMER_TYPE_ID,:g_cCustflag) = :g_cCustflag

AND prm.SERVICE_TYPE=:gServType ----service type

AND prm.PRODUCT_ID IS NULL

AND prm.PRODUCT_FAMILY_ID IS NULL;

store Tab1 into yourpath\Tab1.qvd;

drop table yourpath\Tab1.qvd;

and use it in your code

Else

{

Load *

from yourpath\Tab1.qvd(qvd);

}

hope this helps

Re: How to create QVD from Pl/SQL ?

its_anandrjs — Sun, 23 Mar 2014 15:26:19 GMT

Hi,

After this load script

Else

{

SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER

FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

AND UPPER(prm.PRODUCT_ATTR_SEG)= :g_cProductAttrSeg

AND prm.PRODUCT_ID= :g_iProd_id -- product_id

AND NVL(prm.CHARGE_TYPE,'P')= DECODE(:g_iCharge_type, 1, 'I', 2,'P','P') ---- G_ICHARGE_TYPE 1 OR 2 CAN BE FOUND IN BILL PRODUCT CHARGE

AND NVL(prm.CUSTOMER_TYPE_ID,:g_cCustflag) = :g_cCustflag;

IF(NO_DATA_FOUND)

{ SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER

INTO :g_iGsiRev_Id,:lProductCode,:g_cProfCen,:lDunning_area_desc, :g_cCost_Centre

FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

AND UPPER(prm.PRODUCT_ATTR_SEG)=:g_cProductAttrSeg

AND NVL(prm.CUSTOMER_TYPE_ID,:g_cCustflag) = :g_cCustflag

AND prm.SERVICE_TYPE=:gServType ----service type

AND prm.PRODUCT_ID IS NULL

AND prm.PRODUCT_FAMILY_ID IS NULL;

}

STORE Tablename into Tablename.qvd;

STORE Tablename into $(vYourlocationvariable)\Tablename.qvd;

Put Store command for storing the QVD table like

Re: How to create QVD from Pl/SQL ?

Mon, 24 Mar 2014 05:28:31 GMT

I am not fetching the data from from SQL server,

I am already having the tables in QVD format..

By Using that qvds i have to implement that...

Re: How to create QVD from Pl/SQL ?

its_anandrjs — Mon, 24 Mar 2014 05:36:07 GMT

Hi,

Ok simply load from qvd like

Load *

from Location;

Load

fieldname,

fieldnames......

from Location;

if possible provide me your qvd load script.

Hope this helps

Thanks & Regards

Re: How to create QVD from Pl/SQL ?

Mon, 24 Mar 2014 05:51:34 GMT

I believe you have QVD's available for individual tables

(GSI_PRODUCT_REVENUE_MAP.qvd, GSI_REVENUE_DET.qvd)

To transform your query into a QV Script, would require

>> Knowledge of the whole data model of the application.

>> Understand what exactly the query is doing.

You should break the query into smaller parts and start creating QV Script. or if you are in a hurry to optimize your application, then create a QVD of the result set of this query(as discussed by fellow community members) and use that in your application.

Try transforming it, and share with us, what all problems you face.

Thanks,

Angad

Re: How to create QVD from Pl/SQL ?

Mon, 24 Mar 2014 06:04:22 GMT

All are saying that copy that select Query Code & Paste it..

I have to load the QVD with the Joins, Then I have to mention the conditions...

How its possible..

Re: How to create QVD from Pl/SQL ?

nizamsha — Mon, 24 Mar 2014 06:17:50 GMT

Hi,

Execute your pl/sql procedure or function from your qlikview script.

Your pl/sql procedure should return some result set. In case of function it will return some result set. In case of procedure to return values use ref cursor.

connection_string_to_database

Table1:(Qlikview_Sample_Table)

sql

exec sample_procedure;

store Table1 into Table1.qvd;

Re: How to create QVD from Pl/SQL ?

Mon, 24 Mar 2014 06:30:51 GMT

I am having the script of PL/sql and related QVds involved for that script..

I have to convert that into qvscript...

help me on this...

Re: How to create QVD from Pl/SQL ?

Mon, 24 Mar 2014 06:39:04 GMT

What do you need it to actually do ?

Load already stored/queried information, or, something else ?

Re: How to create QVD from Pl/SQL ?

Mon, 24 Mar 2014 07:22:54 GMT

ok, to start with , use the below:

Since GSI_REVENUE_CODE_ID is common in both, the left join() will join both the table on the common field name.

Now this will be similar to

DB Query:

SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER

FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

QVScript:

GSI_PRODUCT_REVENUE_MAP_tbl:

LOAD

GSI_REVENUE_CODE_ID,

PRODUCT_ATTR_SEG,

PRODUCT_ID,

CHARGE_TYPE,

CUSTOMER_TYPE_ID

..\QVDLocation\GSI_PRODUCT_REVENUE_MAP.qvd

GSI_REVENUE_DET_tbl:

left join(GSI_PRODUCT_REVENUE_MAP_tbl)

LOAD

GSI_REVENUE_CODE_ID,

PRODUCT_CODE,

PROFIT_CENTER,

DUNNING_AREA,

COST_CENTER

..\QVDLocation\GSI_REVENUE_DET.qvd

You can gradually expand the query step by step.

Hope this was helpful.

Thanks,

Angad

Re: How to create QVD from Pl/SQL ?

Mon, 24 Mar 2014 10:01:32 GMT

In all SQL stmts, I want to display only these columns , no need of extra columns..

GSI_REVENUE_CODE_ID,

PRODUCT_CODE,

PROFIT_CENTER,

DUNNING_AREA,

COST_CENTER

Re: How to create QVD from Pl/SQL ?

Mon, 24 Mar 2014 10:20:52 GMT

You can drop the unwanted columns by using

drop field/fields statement in script.

Re: How to create QVD from Pl/SQL ?

Mon, 24 Mar 2014 10:49:35 GMT

Hi Angad,

Can you see the above Script it is having lot of conditions..Can you implement that..

Thanks,

Helen

Re: How to create QVD from Pl/SQL ?

Mon, 24 Mar 2014 11:08:19 GMT

As I earlier said,

To transform your query into a QV Script, we would require

>> Knowledge of the whole data model of the application.

>> Understand what exactly the query is doing.

Did you start breaking the query into smaller pieces and tranforming it?

I would suggest you should try doing it by yourself, and let the community know, where you get stuck.

Hope this was helpful.

Thanks,

Angad

Re: How to create QVD from Pl/SQL ?

ThornOfCrowns — Mon, 24 Mar 2014 11:18:43 GMT

This isn't what you asked for orginally and the longer the thread goes on, the more confusing it becomes.

Can you state clearly:

What you need
What you have
What you have tried
Where it's going wrong

Re: How to create QVD from Pl/SQL ?

arsal_90 — Mon, 24 Mar 2014 11:27:01 GMT

Please upload the sample data file and also share the query that u want to translate in qv script and what is the desired result u want from them also share it.