topic Re: Conversion of Sql script into QV data model. in QlikView

Conversion of Sql script into QV data model.

ajaykumar1 — Wed, 30 Dec 2015 09:42:39 GMT

Hi Everyone,

I have the below SQL query which i need to convert into QV script level.

SELECT

A.ID,

B.NAME,

B.QUANTITY,

B.MODELNUMBER,

G.APPLENAME,

B.IMEIDETAILS,

A.QUOTENUMBER,

A.ORDERPRICE,

B.APPLEINVOICEAMOUNT,

A.ORDERCOMPLETESTATUS,

A.ORDERCREATIONDATE,

A.QUOTECREATIONDATE,

B.PAYMENTDATE,

A.CUSTOMERFIRSTNAME,

A.CUSTOMERCITY,

A.REASON,

C.FIRSTNAME AS STAFFFIRSTNAME,

C.LASTNAME AS STAFFLASTNAME,

D.FIRSTNAME AS RETAILERSHOPNAME,

E.NAME AS RETAILERSTATE,

H.NAME AS RETAILERCOUNTRY,

D.ADDRESS AS RETAILERADDRESS,

D.ZIPCODE AS RETAILERZIPCODE

FROM SHOPPINGBASKET A

INNER JOIN

SHOPPINGBASKETITEM B

ON A.ID = B.TID

INNER JOIN

DETAILS C

ON A.SID=C.SID

INNER JOIN

MERCHANT D

ON C.RID=D.RID

INNER JOIN

STATE E

ON D.STATE=E.STATEID

INNER JOIN STATUS F

ON B.ACSTATUS=F.STATUSID

INNER JOIN MODEL G

ON B.NUMBER=G.MODELID

INNER JOIN PLACE H

ON D.COUNTRY=H.COUNTRYID

More Thanks,

Ajay

Re: Conversion of Sql script into QV data model.

petter — Wed, 30 Dec 2015 09:46:12 GMT

You can first try this:

LOAD

;

SQL

SELECT

A.ID,

B.NAME,

B.QUANTITY,

B.MODELNUMBER,

G.APPLENAME,

B.IMEIDETAILS,

A.QUOTENUMBER,

A.ORDERPRICE,

B.APPLEINVOICEAMOUNT,

A.ORDERCOMPLETESTATUS,

A.ORDERCREATIONDATE,

A.QUOTECREATIONDATE,

B.PAYMENTDATE,

A.CUSTOMERFIRSTNAME,

A.CUSTOMERCITY,

A.REASON,

C.FIRSTNAME AS STAFFFIRSTNAME,

C.LASTNAME AS STAFFLASTNAME,

D.FIRSTNAME AS RETAILERSHOPNAME,

E.NAME AS RETAILERSTATE,

H.NAME AS RETAILERCOUNTRY,

D.ADDRESS AS RETAILERADDRESS,

D.ZIPCODE AS RETAILERZIPCODE

FROM SHOPPINGBASKET A

INNER JOIN

SHOPPINGBASKETITEM B

ON A.ID = B.TID

INNER JOIN

DETAILS C

ON A.SID=C.SID

INNER JOIN

MERCHANT D

ON C.RID=D.RID

INNER JOIN

STATE E

ON D.STATE=E.STATEID

INNER JOIN STATUS F

ON B.ACSTATUS=F.STATUSID

INNER JOIN MODEL G

ON B.NUMBER=G.MODELID

INNER JOIN PLACE H

ON D.COUNTRY=H.COUNTRYID

;

Re: Conversion of Sql script into QV data model.

vikasmahajan — Wed, 30 Dec 2015 09:56:39 GMT

load * ;

select SQL statement

Where condition;

Vikas

Re: Conversion of Sql script into QV data model.

ajaykumar1 — Wed, 30 Dec 2015 09:57:07 GMT

Thanks Peter.

But am getting the same result with Load * and without Load *.(I connected to ODBC as usual)

May i know the difference.?

Re: Conversion of Sql script into QV data model.

vikasmahajan — Wed, 30 Dec 2015 09:57:49 GMT

Qlikview will automatically identify the common columns name for joins.

HTH

Vikas

Re: Conversion of Sql script into QV data model.

PrashantSangle — Wed, 30 Dec 2015 10:01:37 GMT

Hi,

Using

1: Load * - you can use qlikview function in load

2: Without Load * - You can use only SQL functions.

Regards

Re: Conversion of Sql script into QV data model.

Peter_Cammaert — Wed, 30 Dec 2015 10:04:51 GMT

As long as you don't do anything else in the preceding load, there isn't any.

It just serves to show you that in many cases you can use your SQL code as-is in a load script. In addition to that, the main advantage of this approach is that your Database is doing the heavy-lifting for which it is better equipped.

Re: Conversion of Sql script into QV data model.

ajaykumar1 — Wed, 30 Dec 2015 10:16:11 GMT

Yes...In this case am doing any calculations.So not considered the 'Preceding load option'.

But as per as my thoughts its better to execute as same sql query in QV script.

Performance point of view which is better direct executing the sql script or converting into qvd's with data model for small as well as big data volume?

More Thanks,

AJay

Re: Conversion of Sql script into QV data model.

Peter_Cammaert — Wed, 30 Dec 2015 10:32:40 GMT

The SQL code is never executed by QV or related software on the QV platform. It is packaged and sent to the RDBMS through the ODBC/OLEDB interface. The database engine will parse & execute the SQL code, and return all data results to QlikView (if any).

Performance: that depends on the data volume. On average, implementing an incremental load for small or slowly changing tables (e.g. master tables or smaller facts tables) is not worth the effort. Check how long it takes to execute your SQL code without QVD's. Then you can decide on a better approach.

Best,

Peter

Re: Conversion of Sql script into QV data model.

ajaykumar1 — Wed, 30 Dec 2015 11:00:01 GMT

Thanks Peter For detail help.

In this case its taking less time only.I hope no need to create the qvd's if its taking less time.

Ajay

Re: Conversion of Sql script into QV data model.

vikasmahajan — Wed, 30 Dec 2015 11:21:14 GMT

It is always better to create QVD and load data from QVD.

Vikas