Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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

1 Solution

Accepted Solutions
Not applicable

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

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:MMSmiley FrustratedS'),'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:MMSmiley FrustratedS'),'DD-MM-YYYY')) as Date,

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

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

    txtYear as Year,

    type_id,

    update_date

;

SQL SELECT *

FROM Metastorm.dbo.tblBrokerageSystem;     

6 Replies
Not applicable

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

if you paste into script only

OLEDB CONNECT TO ........

Brokerage_Table:

SQL SELECT *

FROM Metastorm.dbo.tblBrokerageSystem;

Load

    AccountExec,

...

...

...

does your script reload succesfully?

Not applicable

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

first of all

you've got

exit Script;

before connection string so your OLE DB script will not execute

Not applicable

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

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.

Not applicable

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

I'm not sure but

try move load statement before sql select

Brokerage_Table:

SQL SELECT *

FROM Metastorm.dbo.tblBrokerageSystem;

Load

    AccountExec,

...

Not applicable

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

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

Not applicable

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

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:MMSmiley FrustratedS'),'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:MMSmiley FrustratedS'),'DD-MM-YYYY')) as Date,

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

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

    txtYear as Year,

    type_id,

    update_date

;

SQL SELECT *

FROM Metastorm.dbo.tblBrokerageSystem;