Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am fairly new to Qlikview and am having issues trying to set-up an application. I'm trying to summarize sales data from an excel spreadsheet. So in my script I am loading the data from the spreadsheet, the file contains 3 fields I'm trying to pull, Store, Qty, and Amount. I then am using a ODBC connection to load info for the Store from 2 tables called Stores and Owners. I need the field Store in the excel sheet to be associated with StoreNumber in the Stores table from SQL. I have a pivot table that I'm summarizing the data in. I can get data by store ok, but via the Stores table and Owners table, I need it summarized by Owner. If I collapse all records it shows the entire total for each owner, if i try to expand all records it show the correct totals by store but lists them all on one owner only. I tried via the script to rename the fileds in the Stores table and the excel spreadsheet so they had an association to to each other. But, when I did that I couldn't get any data in the pivot table. What do I need to do to the correct stores assigned to their respective owners. Thanks for any help.
Rick
Hello Rick
once you join the store,qty and amount from the excel sheet to the stores table maybe by StoreNumber (make sure you have storeNumber in table and excel sheet) then link the stores table to the Owners table, once you have that link then you can place them in the pivot table or straight table but in the calculation maybe you have to write some if statements or Set anylysis.
If possible post one dummy application and somebody can able to help you
Talha
Here is my script statement. I know that Store needs to be linked somehow to StoreNumber, but I tried renaming Store to StoreNumber and then I can't get any results.
LOAD
FROM [OLO sales ending 10-11-2009.xls](biff, embedded labels, table is Sheet1$);
ODBC
LOAD
SQL SELECT * FROM CorpInfo.dbo.tblStores;
LOAD
SQL
Qualify*;
UnQualify
OwnerNumber,You have to link something like this StoreNumber so that the links will be proper then take this fields into the Pivot table.
LOAD
[Store] As StoreNumber, PickupQty, PickupAvg, PickupTotal, DeliveryQty,
DeliveryAvg, DeliveryTotal, TotalQty, TotalAvg, TotalTotal
FROM [OLO sales ending 10-11-2009.xls](biff, embedded labels, table is Sheet1$);
LOAD
StoreNumber, StartDate, EndDate, Address1, Address2, City, State, ZipCode, SitePhone, DataPhone, Fax, SpeedDial, ADI, DmNumber, RfmNumber, NickName, OwnerNumber, MgrName, AsstMgr1, BankCons, Delivery, Co, "Tax_code", DineIn, ConvenienceStore, GiftCards, "Drive_Thru", CurbSide, Buffet, "Carry_Out", Designation, Spif, Spif2, Latitude, Longitude, HyiperlinkDescription, Hyperlink, LocComments, GenComments, eClub, GlutenFree, OnlineOrder, GlutenFreeTnB
SQL SELECT * FROM CorpInfo.dbo.tblStores;
LOAD
OwnerNumber, StartDate, EndDate, FirstName, LastName, Address1, Address2, City,
State, ZipCode, SitePhone, HomePhone, CellPhone, Fax, EmailAddress, CommonName,
CompanyName, Comments, ReserveFactor, RoyaltyPayFreq;
SELECT * FROM CorpInfo.dbo.tblOwners;
Thanks
I had already tried that exactly and it hadn't worked. I got to thinking though the part of the script for the excel formula has LOAD [Store]. I tried changing the script to LOAD [Store] as [StoreNumber] instead of LOAD [Store] as StoreNumber and it worked. The only problem now though is the fields FirstName and LastName from the Owners table are blank in the pivot table. They were listed properly before, but now that I changed it to LOAD [Store] as [StoreNumber] it isn't pulling those fields correctly from the Owners table. Any suggestions as to why that is?
Rick
Rick
I tried to emulate your problem in the attached QV. Is this what you are trying to achieve?
John, I'm still currently using the personal edition so I'm unable to open the application. I'll try to explain what I'm doing.
I have 2 tables like the following.
Store Qty Amount
16001 2 500.00
17001 3 750.00
Owner Store Name
05000 16001 John Doe
07500 17001 Jane Smith
I need to combine these 2 tables so I can see the Owner that the sales belong to
Owner Name Store Qty Amount
05000 John Doe 16001 2 500.00
07500 Jane Smith 17001 3 750.00
As I mentioned by renaming the field for Store in the sales table I was able to get data, but it doesnt pull the info from the owners table, ie. name.
Thanks for your help.
Rick
Sorry Rick - brain not engaged
I've updated the QVW with a license to enable your use as a PE user. You should now be able to open this document.
I basically created an XLS with the same sales data, and an Access db to hold the Owner Master and an Owner-Store master. I also ensured that there were differences in the Store ID label between the entities to force aliasing in the script.
The pivot is then as you describe - owner ID, last name, store, total sales, total units.
Hope that this helps
John,
What do you mean by "I've updated the QVW with a license to enable your use as a PE user" ?
m
John, yes this is what I'm trying to achieve, so I just have to figure out what I have set-up differently that it's not pulling the name correctly form the SQL table for the owner, any ideas on what that would be?