topic Re: Can an application load data from Excel files and a SQL database? in QlikView

Can an application load data from Excel files and a SQL database?

Tue, 04 Dec 2012 12:35:29 GMT

I have an application which loads data each month from QVD files, which are populated from Excel files. My next objective is to make a link between my application and a SQL database, which I have done. However, the application reloads but no data from the SQL database appears only the data from Excel files.

My question is "can a single application load data from Excel files and a SQL database and if so how?"

I have attached my script & application below (without data). The script linking the application to SQL database is named 'Brokerage Table'.

Any help would be appreciated as this is the first time I have linked to a SQL database.

Many thanks.

John

Re: Can an application load data from Excel files and a SQL database?

Tue, 04 Dec 2012 14:08:55 GMT

if you paste into script only

OLEDB CONNECT TO ........

Brokerage_Table:

SQL SELECT *

FROM Metastorm.dbo.tblBrokerageSystem;

Load

AccountExec,

...

does your script reload succesfully?

Re: Can an application load data from Excel files and a SQL database?

Tue, 04 Dec 2012 14:11:28 GMT

first of all

you've got

exit Script;

before connection string so your OLE DB script will not execute

Re: Can an application load data from Excel files and a SQL database?

Tue, 04 Dec 2012 14:26:20 GMT

Thanks Pari Pari, I didn't realise there was an 'Exit Script' function in the previous tab. I have removed this and now the application loads the data from both sources (QVD files from Excel spreadsheets and SQL database).

It doesn't seem to rename the fields though. Is this function available when inporting data from a SQL Database? I need this so fields already in the application can link, for example Inception Date & inception_date, should just be Inception Date.

Thanks again for your help.

Re: Can an application load data from Excel files and a SQL database?

Tue, 04 Dec 2012 15:06:32 GMT

I'm not sure but

try move load statement before sql select

Brokerage_Table:

SQL SELECT *

FROM Metastorm.dbo.tblBrokerageSystem;

Load

AccountExec,

...

Re: Can an application load data from Excel files and a SQL database?

Tue, 04 Dec 2012 15:29:14 GMT

I tried -

LOAD

AccountExec,

...

FROM Metastorm.dbo.tblBrokerageSystem;

SQL SELECT *

But I received an error saying the file cannot be found (as in the SQL database).

With -

Brokerage_Table:

SQL SELECT *

FROM Metastorm.dbo.tblBrokerageSystem;

LOAD

AccountExec,

...

The data is imported from the SQL database but doesn't appear to take any notice (ie. "as [Inception Date]" command).

Any ideas or examples would be great.

Many thanks.

John

Re: Can an application load data from Excel files and a SQL database?

Tue, 04 Dec 2012 18:23:48 GMT

try this...

Brokerage_Table:

Load

AccountExec,

assured as Insured,

brokerage_orig_ccy as [Brokerage Base],

brokerage_sett_ccy as [Brokerage SC],

brokerage_sterling_equiv as [Brokerage GBP],

budget_indicator,

client_ac_no,

client_gross,

client_net,

expiry_date as [Expiry Date],

extract_date as [Extract Date],

import_date as [Import Date],

Import_Unique_Ref,

date(date#(inception_date,'DD/MM/YYYY HH:MM:SS'),'DD-MMM-YYYY') as [Inception Date],

installment_no,

new_renewal,

old_trans_id,

orig_ccy_exch_rate,

orig_ccy_id,

prev_month,

reassured as [Re Insured],

relBudgetGBP,

risk_description,

risk_ref,

sett_ccy_exch_rate,

sett_ccy_id,

stats_1_id,

stats_1_id_official,

stats_1_id_original,

stats_2_id,

stats_2_id_official,

stats_2_id_original,

stats_3_id,

stats_3_id_official,

stats_3_id_original,

stats_4_id,

stats_4_id_official,

stats_4_id_original,

stats_5_id,

stats_5_id_official,

stats_5_id_original,

stats_6_id,

stats_6_id_official,

stats_6_id_original,

stats_7_id,

stats_7_id_official,

stats_7_id_original,

stats_8_id,

stats_8_id_official,

stats_8_id_original,

trans_description,

trans_id,

transaction_entry_date,

transaction_no,

TrueSterlingBkg,

txtBrokerageAccount,

Month(date(date#(extract_date,'DD/MM/YYYY HH:MM:SS'),'DD-MM-YYYY')) as Date,

Month(date(date#(extract_date,'DD/MM/YYYY HH:MM:SS'),'DD-MM-YYYY')) as Month,

year(date(date#(extract_date,'DD/MM/YYYY HH:MM:SS'),'DD-MMM-YYYY')) as Year,

txtYear as Year,

type_id,

update_date

;

SQL SELECT *

FROM Metastorm.dbo.tblBrokerageSystem;