topic Product name from excel spreadsheet in QlikView

Product name from excel spreadsheet

userid128223 — Mon, 13 Feb 2012 16:10:27 GMT

I have excel file with 5 sheets. The sheet name contains product name. Each sheet has CustNo (customer number) that is uniq field across all the sheets.

One customer can exist in multiple products sheet.

How can i create a Product table in Qlikview that will list all its associated customers based on CustNo.

thanks

Product name from excel spreadsheet

Mon, 13 Feb 2012 16:26:31 GMT

Can you post an example of Excel Tables and Expected Result?

Product name from excel spreadsheet

userid128223 — Mon, 13 Feb 2012 16:35:01 GMT

Excel Spreadsheet

1 Worksheet name=Products

Sheet1 name=productA,

Sheet2 name =productB,

Sheet3 name=productC

Sheet4 name=productD

Inside each sheet's (productA,B,C,D) there contains Customers. their name, add etc. One customer can be part of different products.

How can QV show customer by products. so if I select ProductA, QV list all the Customer and fileds associated with it.

thanks

Re: Product name from excel spreadsheet

CELAMBARASAN — Tue, 14 Feb 2012 05:05:07 GMT

Hi,

For this first you need to configure OLEDB or ODBC connection to the Excel file like below

CONNECT TO [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=workbook.xls;Extended Properties="Excel 8.0;"];
or
CONNECT TO workbook;

After load the table (Sheet) names

tables: 
SQLtables;
DISCONNECT; // Don't need ODBC connection anymore

To fetch data from the table with associated products

FOR i = 0 to NoOfRows('tables')-1
  LET productName = peek('TABLE_NAME', i, 'tables');
  Product:    
  LOAD *, 
  '$(productName)' as Product  
  FROM workbook.xls (biff, embedded labels, table is [$(productName)]);
  END IF
NEXT
DROP TABLE tables;

Hope it helps

Celambarasan

Product name from excel spreadsheet

gauravkhare — Thu, 01 Mar 2012 10:26:35 GMT

Hi,

simple answer to your query is: open your QV doc and click on table file tab, now goto your excel file which has several sheets.. you need to select one sheet at a time and you can perform concatenate..

When you select your desired excel file(containing many sheets) it will open a Table Files Wizard Type and in that window you see a dropdown menu named Table.. from that dropdown list select your desire excel file.. if you want to select all excel sheets then you have to perform concatenate after selecting each excel sheet...

Hope this will help you