topic Associating fields between tables in QlikView

Associating fields between tables

Wed, 21 Oct 2009 23:32:19 GMT

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

Associating fields between tables

Wed, 21 Oct 2009 23:56:34 GMT

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

Associating fields between tables

Thu, 22 Oct 2009 02:17:20 GMT

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;

Associating fields between tables

Thu, 22 Oct 2009 03:15:35 GMT

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

Associating fields between tables

Thu, 22 Oct 2009 04:06:45 GMT

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

Associating fields between tables

Qlik_Trigg — Tue, 27 Oct 2009 06:16:47 GMT

Rick

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

Associating fields between tables

Tue, 27 Oct 2009 19:24:37 GMT

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

Associating fields between tables

Qlik_Trigg — Tue, 27 Oct 2009 19:40:55 GMT

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

Associating fields between tables

mongolu — Tue, 27 Oct 2009 19:47:57 GMT

John,

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

Associating fields between tables

Tue, 27 Oct 2009 20:07:14 GMT

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?

Associating fields between tables

Qlik_Trigg — Tue, 27 Oct 2009 22:56:32 GMT

can you take a screen shot of the table viewer so we can ensure that relationships are correct (ctrl T from either script editor or UI designer)? I was trying to look for elements that might be creating non-required relationships between your tables & causing errors.

Associating fields between tables

Tue, 27 Oct 2009 23:21:11 GMT

In my script I have the following.

Qualify *;

Unqualify OwnerNumber,

Thanks Rick

Associating fields between tables

Qlik_Trigg — Wed, 28 Oct 2009 00:10:06 GMT

Rick

I'd recommend dropping the Qualify/Unqualify in this instance and focus just on ensuring that Stores and Owners are linked just by OwnerID. It seems that the Store table has either copied the address elements from the Owner table into itself (allow overrides??) or has its own address structure. In this case, either

1. eliminating the fields fromthe Store select statement so that all address information comes from the Owner table

2. aliasing the fields in the Store select to be unique to the Store table

should ensure that you have a link between Store and Owner on just OwnerID. Remember QlikView creates joins on identical field names in different entities - the collection of duplicate fields between Stores and Owner creates the SYNTHETIC key table you see in your table viewer. We want to eliminate that synthetic table in this case.

Let me know how this works

Associating fields between tables

Wed, 28 Oct 2009 01:30:53 GMT

I'm a little confused I thought that's what the Qualify/Unqualify was suppose to do. I'm using the same Owners and Stores table in another application and that is how I have it set-up and it works. I'm trying to go into the script and make the change, but now the application locks up on me whenever I try to reload it.

Associating fields between tables

Qlik_Trigg — Wed, 28 Oct 2009 01:54:26 GMT

You are spot on about what qualify and unqualify are meant to do, but in this instance my request was to see if we can get this working with the manual edits. The fact that the same script is working in another application indicates we can get this back together; it maybe down to position in the script (i.e. between the two loads in the above snippet you provided) of the qualify/unqualify statements.

No clues on the reload hanging though, sorry

Associating fields between tables

Thu, 29 Oct 2009 01:23:31 GMT


Mihai Petcu wrote:
John,
What do you mean by "I've updated the QVW with a license to enable your use as a PE user" ?
m<div></div>

Mihai,

As QlikTech employees, we have the ability to embed licenses into specific QlikView documents so they can be opened by any QlikView user. Otherwise, a license is required to open QlikView files created by another user.

Associating fields between tables

mongolu — Thu, 29 Oct 2009 02:41:05 GMT

Yes, Jason.

I've got the response also from John Trigg, on private.

Thanks for your support.

Associating fields between tables

Thu, 29 Oct 2009 03:10:55 GMT

John, thanks for your help! I got the report to work the way I wanted.

Is there an issue with cutting and pasting the code when trying to change the order in the edit script? I figured out the problem was the order where the Qualify/Unqualify statement was at. No matter what I did though every time I tried to reload my application it would lock up and freeze. So I finally just started from scratch and did a new application and recreated the report I wanted and everything worked perfectly and the report produced exactly what I wanted. Could the application be locking up since I tried to edit the script by cutting and pasting the code in a different order or was it more than likely a different issue?

Associating fields between tables

Qlik_Trigg — Fri, 30 Oct 2009 18:07:12 GMT

Rick

I am glad to hear that the application is working now. I suspect something different causing the reload to hang. I'll pass this along to see if there is anything that is suspect.

Regards

John T