Conversion of Sql script into QV data model.

[ajaykumar1]

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

[petter]

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

;

[vikasmahajan]

load * ;

select SQL statement

Where condition;

Vikas

[ajaykumar1]

Thanks Peter.

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

May i know the difference.?

[vikasmahajan]

Qlikview will automatically identify the common columns name for joins.

HTH

Vikas

[PrashantSangle]

Hi,

Using

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

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

Regards

[Peter_Cammaert]

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.

[ajaykumar1]

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

[Peter_Cammaert · Accepted solution]

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

[ajaykumar1]

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