Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Mahamed_Qlik
Specialist
Specialist

Functions in sql query

Hi All,

Below given query hits my reload time by 3days..

SELECT top 100 TWS_MIS.mis.ConvertCurrency(TWS_MIS.mis.TWS_ItinHotel.bookingid,TWS_MIS.mis.TWS_ItinHotel.rateamount,TWS_MIS.mis.TWS_ItinHotel.ratecurrency,'GBP',TWS_MIS.mis.TWS_Header.bkcreated) RATEAMOUNTGBP

,TWS_MIS.mis.TWS_ItinHotel.*

FROM TWS_MIS.mis.TWS_ItinHotel left join TWS_MIS.mis.TWS_Header

ON (TWS_MIS.mis.TWS_ItinHotel.BookingId=TWS_MIS.mis.TWS_Header.BookingId);

The main thing in the query is I am calling the function "TWS_MIS.mis.ConvertCurrency" which hips up the reload time.

Note: Query works proper in sql server.

Kindly advise for the best solution for this.

Regards,

Mahamed

11 Replies
vikasmahajan

hits my reload time by 3days ? where you are loading in qlik ?

if yes then really this is very bad , you can create a cursor into sql load data into qv from cursor it may faster.

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
avinashelite

One solution would be : To handle the ConvertCurrency logic in Qlik ..so that the reload time gets reduced and Qlikview had effective ways to handle the conversion scenario

Mahamed_Qlik
Specialist
Specialist
Author

Hi Vikas,

Tell me the one.

Mahamed_Qlik
Specialist
Specialist
Author

Hi vikas,

I running the query in initial load app with connectivity to sql.

And let me know how do we can create the cursor?

avinashelite

Try to build the currency conversion logic in Qlikview instead of handling the same in SQL query... so that SQL query will run properly

vikasmahajan

you do one thing  create logic of currency into qlikview as Avinash said  loading data into qv is always faster without any joins , Load 2 tables and create qvd . load this qvd into qvw and build logic of currency into qlikview itself  rather than apply logic in sql.

HTH

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
Mahamed_Qlik
Specialist
Specialist
Author

Hi Vikas,

So you mean, It is always better to apply the logic in qlik than the sql query?

Regards,

Mahamed

Peter_Cammaert
Partner - Champion III
Partner - Champion III

This is weird. If you copy the exact same query from sql server (management studiio) into QlikView load script, it should work in exactly the same way. On condition that you use the same connection, machine, account, privileges and database. All surrounding factors.

Whether you launch this query from a sql server tool or from QlikView load script, the SELECT (including any calls to stored procedures) is always passed on to and executed by the Database engine, not by QlikView or by the sql server tool.

For example, try to execute the CALL to the function straight from the QlikView load script using SQL EXEC and appropriate parameters. Do you get any results from a single call?

Peter

Mahamed_Qlik
Specialist
Specialist
Author

Hi Peter,

Can you please let me know more about how to use SQL EXEC and CALL from Qlikvire script?

Regards,

Mahamed