MSSQL data Load in Qlikview.

[#1]

Hi,

We have implemented a small project using Excel as datasource and nw we are trying to use MSSQL as our database. We are able to connect and retrieve records from MSSQL.

However we are requried to filter data while loading the script (LOAD command). The following command worked successfully when excel was our datasource but the same script is not working when datarouce is MSSQL.

SnapShot:

LOAD snapshotdate,

     potentialid,

     snap_amount,

     snap_closingdate,

     Ceil(Month(snap_closingdate)/3) as Quarter,

     sales_stage as snap_salesstage

FROM

Tables\snapshot.xlsx

(ooxml, embedded labels, table is Sheet1) where (date(snap_closingdate)-date(snapshotdate)<=90) and

(snap_closingdate>=snapshotdate);

Potential:

LOAD potentialid,

     related_to as accountid,

     potentialname,

     pot_amount,

Month(pot_closingdate) as P_Month,

     pot_closingdate as pot_closingdate1,

if(floor(pot_closingdate)>=floor(today()) and  num(floor(pot_closingdate))-num(floor(today()))<=90,pot_closingdate) as

pot_closingdate,

if(floor(pot_closingdate)>=floor(today()) and num(floor(pot_closingdate))-num(floor(today()))<=90, today()) as

Todaysdate,

    if(floor(pot_closingdate)>=floor(today()) and 

num(floor(pot_closingdate))-num(floor(today()))<=90,Month(pot_closingdate)) as PMonth,

if(monthstart(pot_closingdate)>=addmonths(monthstart(today()),-12),dual(

Month(pot_closingdate)&'-'&year(pot_closingdate),monthstart(pot_closingdate))) as MonthYear,

     sales_stage as pot_salesstage,

     Ceil(Month(pot_closingdate)/3) as PQuarter

FROM

Tables\potential.xlsx

  (ooxml, embedded labels, table is Sheet1) ;

Thanks in Advance.

[#2]

LOAD is for local files (eg Excel). For databases use SELECT. Exactly as you do a query in Query Analyzer.

[Miguel_Angel_Baeyens]

Hi Lucian,

For what it's worth, note that that is not correct for two reasons: 1) Excel spreadsheets can be opened using OLE DB or ODBC drivers instead of as plain files, and 2) SQL loads do need the LOAD statement even if you don't state it. If you do not use it, then QlikView will put it there for you.

What you SELECT is what you are pulling from the database, and mut be DB compliant, meaning that variables, syntax, and all that stuff will depend on the DB on the other side, whilst what you LOAD is what you put into QlikView memory. All the dozens of functions available for LOAD are not in the SELECT, as it depends on the driver, permissions, the RDBM version... Think of doing a SELECT * and LOAD only two fields.

In short, use LOAD always to make sure that what you see in QlikView is what you want to see.

Hope that helps.

Miguel

[Miguel_Angel_Baeyens]

Hi,

Most RDBMs are case sensitive, so if in the database a field is stored as "CUSTOMER" and you LOAD "Customer" you will get an error: QlikView is case sensitive in both names of fields and values. Make sure that you are using the wizard in the Select button once you have set your database connection and tested it successfully with the "Connection" button in the script editor.

Hope that helps.

Miguel