topic Issue linking with bridge tables in QlikView

Issue linking with bridge tables

Sat, 18 Jul 2009 06:27:50 GMT

I have three Database tables (views, actually) Members, Claims, and Rewards.

Rewards carries a reference to members.

Claims carries a reference to Members as well.

Claims and rewards have a complicated relationship. Not all rewards are a result of claims, and a single claim may result in more than one reward. In the underlying database there are intermediary transactional tables that define the relationship between claims and rewards. (it requires that I traverse two intermediary tables to get from claim to reward)

I've created a view which by convention I've called Claims__Rewards. Effectively, this view takes the place of a bridge table, so each record in the view contains a reference to a claim, and a reward.

When I attempt to load these 4 views into QlickView, the script runs successfully, but after loading all of the records, it tells me that the script failed, without any further verbose description of the problem. Stepping through the code yields no errors until after the last line of the script, where I get the same failure message.

By way of debugging, I added a reference on the bridge table to the members table, to see what would happen. When I load all of these tables into QlickView, I get no errors at all, and all of the tables are linked (looking at the table linking view, I see that the application has created some additional bridge tables of its own to rationalize the relationship) However, the functionality I want (which is to identify a specific claim, and be able to see only the rewards that resulted from it) doesn't work. Instead, by selecting a specific claim record, I see all rewards for that user. (which makes sense based on the table structure I'm seeing).

I can certainly include the script I'm working with, if that's helpful, but I think that the problem is logical, rather than syntactical.

Issue linking with bridge tables

Sat, 18 Jul 2009 06:51:09 GMT

I just tested using an entirely new project, linking just claims, rewards, and claim__reward, I still get the same error message.

Issue linking with bridge tables

Oleg_Troyansky — Sat, 18 Jul 2009 19:59:55 GMT

please post a sample QVW if possible

Oleg

Issue linking with bridge tables

Tue, 21 Jul 2009 07:16:42 GMT

Hi Oleg, I'm afraid that I can't post the QVW. Even my development environment's data is sensitive, and I'm only just learning the product, so I'm not exaclty sure how I'd create a dummy. It's very possible that I'm either not doing something correctly, or missing some elementary step. I've run into the same problem in a number of places now with my data, with the same failure behavior, where the script completes, and then bails afterwards.

Issue linking with bridge tables

Anonymous — Tue, 21 Jul 2009 07:33:09 GMT

Fry,
You can scramble the data (see document properties). In this particular case, what is needed is the script. If you cannot share the actaul script, create a similar one, it may be good enough.

Issue linking with bridge tables

Tue, 21 Jul 2009 23:10:41 GMT

The script I can share.

As I said, the script executes all the way through debug, without failing until I close the debug window.

This is the script from my 'quick' sample which fails in the same way.


//ommited connect to, standard OLE DB connection to Oracle.
SQL SELECT AGREEDTOTERMS,
 AREACODE,
 CLIENTACCOUNTSTATUSCODE MEMBER_ELIGIBILITY,
 EMAILADDRESS,
 FAXNUMBER,
 FIRSTNAME,
 FULLNAME MEMBER_NAME,
 ID MEMBERID,
 JOBTITLE,
 LASTNAME,
 MEMBERROLE,
 MOBILENUMBER,
 PRIMARYIDENTIFIER USER_PI,
 REGISTRATIONCOMPLETED,
 substr(REGISTRATIONDATE,1,4) RegistrationYear,
 substr(REGISTRATIONDATE,5,2) RegistrationMonth,
 substr(REGISTRATIONDATE,7,2) RegistrationDay,
 to_date(substr(registrationdate,1,8), 'yyyymmdd') RegistrationDate,
 TELEPHONENUMBER,
 VARCOMPANYID
FROM "SCHEMA"."MEMBER";
SQL SELECT CLAIMID,
 CLAIMNUMBER,
 CLAIMSTATUSCODE,
 DENIALREASONCODE,
 DISTRIBUTORID,
 DISTRIBUTORINVOICENUMBER,
 TRANSACTIONDATE CLAIM_TRANSACTIONDATE,
 USERID MEMBERID,
 USERPURCHASEID,
 V_ID as UserClaimID
FROM "SCHEMA"."USER_CLAIMS"
WHERE V_ID is not null;
SQL SELECT EARNEDDATE REWARD_EARN_DATE,
 ISSUEDATE REWARD_ISSUED_DATE,
 MESSAGECODE,
 MESSAGEMERGEVALUE,
 REVERSED,
 REWARDDENIALREASONCODE,
 REWARDDESCRIPTION,
 REWARDQUANTITY,
 REWARDSTATUS,
 SUMMARIZATIONCODE,
 TRANSACTIONDATE REWARD_TRANSDATE,
 //USERID MEMBERID,
 V_ID as UserRewardID,
 MESSAGEMERGEVALUE as claimNumber
FROM "SCHEMA"."USER_REWARD"
WHERE V_ID is not null;
SQL SELECT
UserRewardID,
UserClaimID
FROM "SCHEMA"."EMER_CLAIM__REWARD";

Issue linking with bridge tables

rwunderlich — Wed, 22 Jul 2009 02:13:07 GMT


Fry wrote:Even my development environment's data is sensitive

See this wiki page for tips on uploading sensitive data:
http://community.qlik.com/wikis/qlikview-wiki/preparing-examples-for-upload-reduction-and-data-scrambling.aspx

-Rob

Issue linking with bridge tables

Wed, 22 Jul 2009 02:51:25 GMT

Hi Rob,

Thanks for the info! But I'm afraid the QVW wouldn't be a lot of use because the data isn't actually in it. It fails before the data gets loaded. I did post the script above.

Issue linking with bridge tables

pablolabbe — Wed, 22 Jul 2009 03:14:16 GMT

You can post a print screen of table layout for clear understanding of the generated model ?

Issue linking with bridge tables

Wed, 22 Jul 2009 03:28:51 GMT

The top row of tables. There is a bridge that belongs between claims, and rewards. Attempts to load the bridge fail.

Issue linking with bridge tables

Wed, 22 Jul 2009 06:24:53 GMT

Silly Analyst! Links are for Kids!

The problem is that the relationship I'm attempting to introduce causes a circular reference. It's a logical issue with the data model, and I think I just need to decide which relationship is more important. It may also be possible to create a seperate document for claims-purchase analysis where the relationship can be explicitly defined. -- I need to change the way I think about data structures.

Basically, I don't think I can have it both ways.

Issue linking with bridge tables

Anonymous — Wed, 22 Jul 2009 07:02:25 GMT

Fry,
From the incomplete information you provided (the data model doesn't match the script), you got at least one loop here. notice MEMBERID in MEMBER, USER_CLAIMS, and USER_REWARD, and at the same time USER_CLAIMS and USER_REWARD connected by the last SELECT in your script (not in the data model). Usually you don't have the result that the reload collapses, but it may happen.
I'd recommend to review the data model, probbaly join some logical tables to avoid the loops.

Edit:
I see you got it already while I was looking into the script...
You don't have to break it into two aplications, although you can if it's your preference. The common approach is to join or concatenate transactions table. For example, consider concatenating USER_CLAIMS and USER_REWARD into one table. See if it fits your business needs.

Issue linking with bridge tables

Wed, 22 Jul 2009 23:18:41 GMT

Thanks everyone for your help!