Converting SQL results (typically excel) to QVD to be used in QlikV

[Anonymous]

Hi,

I need to run a SQL script on an external database and then translate these results into .qvd to use in QlikView.  I have searched the community to understand how to do this.  There is lots and lots of information out there and I am getting a little confused.  Does anyone have or know of a demo I could watch to better understand how to run the full process?

Cheers,

Laura

[Siva_Sankar]

For more information Refer here Creating QVD's

There are some you tube videos referred in the discussion. It will help you a lot.

[Anonymous]

Thanks. I am loading an .xls file and I am getting an error starting with “Syntax error”, missing/misplaced FROM:” etc

The script ran fine so where/how can I diagnose this error?

Laura Castagna

Business Systems and Process Analyst, NSW Business Chamber

140 Arthur Street North Sydney NSW 2060

Tel: 02 9458 7804 | Mob: 0429 486 934 | Web: www.nswbusinesschamber.com.au<http://www.nswbusinesschamber.com.au>

Twitter<http://www.twitter.com/nswbc> | Facebook<https://www.facebook.com/NSWBusinessChamber> | LinkedIn<http://www.linkedin.com/company/388425?trk=saber_s000001e_1000> | YouTube<http://www.youtube.com/nswbctv>

<http://www.nswbusinesschamber.com.au/>

[Siva_Sankar]

Check whether you have given appropriate spaces, commas and semicolons in your script.

Check the syntax:

Eg for syntax purposes

LOAD

     now() as Now,

     Day(Date) as Day,

     Month(Date) as Month,

     Year(Date) as Month,

     Description,

     mid(Description, 1, 3) as Prefix

     ;

SQL SELECT

     Date,

     Description

FROM dbo.MyTable;

[Siva_Sankar]

Not sure about your script to find the exact mistake that you have done in your script. possible to share?

[Anonymous]

But it didn’t come up with an error when the script was run though? here the script.

select

FieldOfficer.first_name ||' '||FieldOfficer.

surname as "Field Officer",--field_officer_id,

signed_on as "Date Training Contract Signed",

ss.name as "Status",

signup_sub_status_id as "SubStatus",

commencementdate as "Date of commencement",

appdateofbirth as "Date of Birth",

employerlegalname as "Employer Legal Name",

employertradingname as "Employer Trading Name",

tc.id as "Identification Number",

received_on as "NTC Received Date",

tyims_employerid as "Employer TYIMS ID",

AccountManager.first_name ||' '||AccountManager.surname as "Account Manager",

tc.created_at as "@Created Date",

BDLRefferedBy.first_name ||' '||BDLRefferedBy.surname as "BDL Referred By",

last_kpi_action_date as "Status Date of change",

employer_id as "Employer ID",

--created_by as "Created User",

appgivenname ||' '||appsurname as "Client Full Name",

rtoname as "Name of RTO",

rtoname as "RTO Name",

rtocode as "RTO NTIS CODE",

heard_about_us_id as "Where Hear"

from training_contracts tc

left join signup_statuses ss on tc.signup_status_id = ss.id

left join users FieldOfficer on FieldOfficer.id = tc.field_officer_id

Left join users AccountManager on AccountManager.id = tc.account_manager_id

left join users BDLRefferedBy on BDLRefferedBy.id = tc.bdl_referred_by_id

where signed_on >= '2014-07-01'

Laura Castagna

Business Systems and Process Analyst, NSW Business Chamber

140 Arthur Street North Sydney NSW 2060

Tel: 02 9458 7804 | Mob: 0429 486 934 | Web: www.nswbusinesschamber.com.au<http://www.nswbusinesschamber.com.au>

Twitter<http://www.twitter.com/nswbc> | Facebook<https://www.facebook.com/NSWBusinessChamber> | LinkedIn<http://www.linkedin.com/company/388425?trk=saber_s000001e_1000> | YouTube<http://www.youtube.com/nswbctv>

<http://www.nswbusinesschamber.com.au/>

[Siva_Sankar]

You said you encountered error while loading an excel file. But the script you copied is not  loading from excel file.

[Anonymous]

I sent you wrong script:-

LOAD ,

,

SignUpStatus,

SignUpSubStatus,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

FROM

F:\SQL\SignUpTask.xls

(biff, embedded labels, table is Sheet0$);

Laura Castagna

Business Systems and Process Analyst, NSW Business Chamber

140 Arthur Street North Sydney NSW 2060

Tel: 02 9458 7804 | Mob: 0429 486 934 | Web: www.nswbusinesschamber.com.au<http://www.nswbusinesschamber.com.au>

Twitter<http://www.twitter.com/nswbc> | Facebook<https://www.facebook.com/NSWBusinessChamber> | LinkedIn<http://www.linkedin.com/company/388425?trk=saber_s000001e_1000> | YouTube<http://www.youtube.com/nswbctv>

<http://www.nswbusinesschamber.com.au/>

[jagan]

Hi,

Can you attach the Excel file and the Qlikview file you are working?

Regards,

Jagan.

[Siva_Sankar]

Make sure there is no comma after LOAD

Wrong script:

Load,

field1,

FROM

F:\SQL\SignUpTask.xls

(biff, embedded labels, table is Sheet0$);

Correct script:

Load field1,

                       field2,

                       field3

FROM

F:\SQL\SignUpTask.xls

(biff, embedded labels, table is Sheet0$);

[Anonymous]

Thanks. Checked that. Still no joy

Laura Castagna

Business Systems and Process Analyst, NSW Business Chamber

140 Arthur Street North Sydney NSW 2060

Tel: 02 9458 7804 | Mob: 0429 486 934 | Web: www.nswbusinesschamber.com.au<http://www.nswbusinesschamber.com.au>

Twitter<http://www.twitter.com/nswbc> | Facebook<https://www.facebook.com/NSWBusinessChamber> | LinkedIn<http://www.linkedin.com/company/388425?trk=saber_s000001e_1000> | YouTube<http://www.youtube.com/nswbctv>

<http://www.nswbusinesschamber.com.au/>