Product name from excel spreadsheet

[userid128223]

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

[#2]

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

[userid128223]

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

[CELAMBARASAN · Accepted solution]

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

[gauravkhare]

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