3 Replies Latest reply: Mar 2, 2016 7:54 PM by Stephen Broomhead RSS

    Loading Excel and ODBC data into qlik sense

    Stephen Broomhead

      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)