Loading Excel and ODBC data into qlik sense

Sun, 28 Feb 2016 13:48:02 GMT

I have created an APP and created an ODBC connection, pasted an SQL script into the data load editor and all works fine.

I then created a link to an Excel file and tried to load that data into the same APP and I get the error

Data has not been loaded. Please correct the error and try loading again.

App successfully saved.

I then created a new app and loaded the excel data successfully.

Can anyone help me identify the problem.

The complete script in the data load editor is for the ODBC

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-GB';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

LIB CONNECT TO 'Bistrack';

select
year(fy.Enddate) 'FinancialYear',
format(sa3.FinancialPeriodNumber, '00') 'Period',
convert(varchar(10),sa3.DocumentDate,111) 'Doc Date',
sr.name 'Sales Rep',
b1.name 'Branch',
b3.name ' Issuing Branch',

isnull(supplier.name,' No Supplier') 'Supplier',
--replace(replace(isnull(ol.manualcode,product.productcode),char(10),' ' ), char(13),' ' ) 'Product',

product.productcode,
--'"',
replace(replace ((case when sa3.documenttype = 'Invoice' then isnull(ol.manualdescription,product.description)
else isnull(cnl.manualdescription,product.description) end), CHAR(10),''),CHar(13),' ') as 'Decription',
--'"' ,
--Product Groups and Attributes

PG1.Name AS ProductGroup1 ,
PG2.Name AS ProductGroup2 ,
PG3.Name AS ProductGroup3 ,

product.udfRangeName As 'Range',
product.udfrangestyle As 'Style',
product.udfexclusivity as 'Exclusivity',
product.udfpriceclass as 'Price Class' ,
product.udfmaterial as 'Material' ,
product.udfsize as 'Size' ,
product.udfshape as 'Shape',
product.udffinishcolour as 'Colour',

case when sa3.DocumentType = 'Invoice' then format(round((OL.Quantity * OL.StockPerConversion),2),'###.##')
else format((cnL.Quantity),'###.##') end as 'Qty Desp' , --need to ensure sales are all represented in the same units

isnull(stockper.percode,per.percode) as 'Per',

case when sa3.documenttype = 'Invoice' then ol.unitsellprice * (OL.Quantity * OL.StockPerConversion) else sa3.TotalSales end 'Total Undisc Price',
sa3.totalsales 'SA-Total Sell',
'MPV' = sa3.totalinternalcost - sa3.totalcost,
sa3.totalcost 'SA-Total Cost',
sa3.totalinternalcost 'SA-Total IntCost',

sa3.DocumentType 'Doc Type',

'Stock Type' = case
     when left(product.productcode,3) = 'ZZC' then 'Catalogue Special'
     when left(product.productcode,3) = 'ZZZ' then 'Opal Dead Special'
     when left(product.productcode,2) = 'ZZ' then 'Generic Special'
     when left(product.productcode,1) = 'Z' then 'Defined Special'
     when product.producttype <>1 then 'Other'
     else 'Stock' End ,

CASE when sa3.DocumentType = 'Invoice' then isnull(oh.ordernumber,sa3.documentid) else cnh.CreditNoteNumber end as 'Doc No',
CASE when sa3.DocumentType = 'Invoice' then ol.linenumber else cnl.linenumber end as 'Line No' ,

--'DocFrom' = case when isnull(olp.orderlineid,0)=0 THEN 'Opal' else 'BT' End,

ol.discount1 'Rep Disc1',
ol.discount2 'Rep Disc2',
ct.name 'Customertype',
customer.name 'Customer',
--customer.customertypeid,
case when customer.cashaccount = 0 then 'Credit' else 'Cash' end as 'Cash a/c',

'Premier a/c' = case when customer.customertypeid = 5 then 'Retail'
when customer.cashaccount = 1 then 'Trade Cash'
else 'Trade Credit' end,
sa.name 'Sales Area',
st.name 'Sales type',

customer.customercode 'Cust Code',
(customer.customercode + '~' + dbo.psiPostcodeFromString(isnull(oh.deliveryaddress,isnull(croh.deliveryaddress,cnh.invoiceaddress)))) as 'Property',
dbo.psiPostcodeFromString(isnull(oh.deliveryaddress,isnull(croh.deliveryaddress,cnh.invoiceaddress))) 'Delivery Pcode',
REPLACE(REPLACE(isnull(oh.deliveryaddress,isnull(croh.deliveryaddress,cnh.invoiceaddress)),CHAR(10),' '),CHAR(13),' ') 'Delivery Address',
r.name ' Region',
case when product.udfexclusivity in ('CPH Branded','Exclusive','Semi Exclusive') then 'Obese' else 'Non-Obese' end as 'Obese'

from FinancialYear fy WITH(NOLOCK)
left join SalesAnalysis3 sa3 WITH (NOLOCK) on fy.FinancialYearID = sa3.FinancialYearID
LEFT JOIN InvoiceHeader IVH WITH(NOLOCK) ON SA3.DocumentID = IVH.InvoiceID and sa3.DocumentType = 'Invoice'
left join invoiceline as ivl on ivl.InvoiceLineID = sa3.DocumentLineID and sa3.DocumentType = 'Invoice'
left join orderheader as oh with (nolock) on ivh.orderid = oh.orderid
left join orderline as ol with (nolock) on ivl.orderlineid = ol.orderlineid
LEFT JOIN CostAdjustmentHeader CAH WITH(NOLOCK) ON SA3.DocumentID = CAH.CostAdjustmentID
LEFT JOIN Branch AS B4 WITH(NOLOCK) ON B4.BranchID = SA3.HomeBranchID
LEFT JOIN Branch AS B3 WITH(NOLOCK) ON B3.BranchID = SA3.IssuingBranchID
LEFT JOIN Branch AS B1 WITH(NOLOCK) ON B1.BranchID = SA3.BranchID
LEFT join product on sa3.productid = product.productid
LEFT join supplier WITH(NOLOCK) on supplier.supplierid = product.supplierid
left join SalesRep as sr with (nolock) on sa3.salesrepid = sr.salesrepid

left join orderlinepriceinfo as olp with(nolock) on ivl.orderlineid = olp.orderlineid
left join region as r with(nolock) on r.regionid = b1.regionid
left join productprice as pp with(nolock) on sa3.productid = pp.productid and 0 = pp.branchid and pp.length = 0
left join productprice as ppo with(nolock) on sa3.productid = ppo.productid and ppo.branchid =isnull(sa3.IssuingBranchID, sa3.OrderingBranchID) and ppo.length=0
left join customer with(nolock) on sa3.customerid = customer.customerid
left join customertype as ct with(nolock) on customer.customertypeid = ct .customertypeid
left join customerfinancial as cf with (nolock) on cf.customerid = customer.customerid
LEFT JOIN ProductGroup AS PG1 WITH(NOLOCK) ON PG1.ProductGroupID = sa3.ProductGroup1ID
LEFT JOIN ProductGroup AS PG2 WITH(NOLOCK) ON PG2.ProductGroupID = sa3.ProductGroup2ID
LEFT JOIN ProductGroup AS PG3 WITH(NOLOCK) ON PG3.ProductGroupID = sa3.ProductGroup3ID
left join saletype as st with (nolock) on sa3.SaleType = st.SaleTypeID
left join salesarea as sa with(nolock) on sa3.salesareaid = sa.salesareaid

left join per as stockper on stockper.perid = ol.CostPerID

left join CreditNoteLine as cnl with(nolock) on cnl.CreditNoteLineID = sa3.documentlineid
and cnl.creditnoteid = sa3.documentid
and sa3.documenttype = 'Credit'

left join CreditNoteHeader as cnh with (nolock) on cnh.CreditNoteID = cnl.CreditNoteID
left join orderheader as croh with(nolock) on cnh.orderid = croh.orderID
left join per with(nolock) on per.perid = isnull(ol.inputPerID,cnl.inputperid)

where sa3.financialyearid >= 6 -- >=2014

AND FOR THE EXCEL FILE

LOAD

[FinancialYear],

[Period],

[PdIndex]

FROM [lib://Sales (Despatches) starting from 2014/Financial Period Index.xlsx]

(ooxml, embedded labels, table is Sheet1)

Re: Loading Excel and ODBC data into qlik sense

Lucke_Hallbergson — Wed, 02 Mar 2016 08:16:13 GMT

Are You not missing a ";" after "(ooxml, embedded labels, table is Sheet1)"?

Eg, row ought to read;

(ooxml, embedded labels, table is Sheet1);

And shouldn't the load statement also be preceded by a table name, eg;

whatevername:

LOAD

Re: Loading Excel and ODBC data into qlik sense

Wed, 02 Mar 2016 16:05:16 GMT

also missing a ; at the end of the ODBC script.

I think the ODBC script requires SQL before the SELECT

e.g.

LIB CONNECT TO 'Bistrack';

SQL
select
year(fy.Enddate) 'FinancialYear',

.....

where sa3.financialyearid >= 6 -- >=2014;

Re: Loading Excel and ODBC data into qlik sense

Thu, 03 Mar 2016 00:54:29 GMT

Thank you - it's now fixed

topic Re: Loading Excel and ODBC data into qlik sense in Connectivity & Data Prep

Loading Excel and ODBC data into qlik sense

Re: Loading Excel and ODBC data into qlik sense

Re: Loading Excel and ODBC data into qlik sense

Re: Loading Excel and ODBC data into qlik sense