Associating fields between tables

[#1]

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

[#2]

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

[#3]

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,

SELECT * FROM CorpInfo.dbo.tblOwners;OwnerNumber, StartDate, EndDate, FirstName, LastName, Address1, Address2, City, State, ZipCode, SitePhone, HomePhone, CellPhone, Fax, EmailAddress, CommonName, CompanyName, Comments, ReserveFactor, RoyaltyPayFreq;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; CONNECT TO CorpInfo; [Store], PickupQty, PickupAvg, PickupTotal, DeliveryQty, DeliveryAvg, DeliveryTotal, TotalQty, TotalAvg, TotalTotal;

[#4]

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

Talha

[#5]

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

[Qlik_Trigg]

Rick

I tried to emulate your problem in the attached QV. Is this what you are trying to achieve?

[#7]

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

[Qlik_Trigg]

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

[mongolu]

John,

What do you mean by "I've updated the QVW with a license to enable your use as a PE user" ?

m

[#10]

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?