18 Replies Latest reply: Oct 30, 2009 2:07 PM by John Trigg RSS

    Associating fields between tables

      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

          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

               

               

               

               

               





               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               



               

               

              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

                   

                  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

                      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
                  John Trigg

                  Rick

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

                    • Associating fields between tables

                      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
                          John Trigg

                          Sorry Rick - brain not engaged Tongue Tied

                          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
                              Mihai Petcu

                              John,

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

                              m

                                • Associating fields between tables
                                  Community Administrator

                                   


                                  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
                                      Mihai Petcu

                                      Yes, Jason.

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

                                      Thanks for your support.

                                        • Associating fields between tables

                                          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

                                      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
                                          John Trigg

                                          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

                                              In my script I have the following.

                                              Qualify *;

                                              Unqualify OwnerNumber,

                                              Thanks Rick

                                                • Associating fields between tables
                                                  John Trigg

                                                  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

                                                  or

                                                  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

                                                      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
                                                          John Trigg

                                                          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