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

[Qlik_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.

[#3]

In my script I have the following.

Qualify *;

Unqualify OwnerNumber,

Thanks Rick

[Qlik_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

[#5]

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.

[Qlik_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

[#7]

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.

[mongolu]

Yes, Jason.

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

Thanks for your support.

[#9]

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?

[Qlik_Trigg]

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