Not sure of the best way to describe this - I am trying to join 3 tables to a 4th using a field 'USER_ID'. I want to be able to filter the data based on USER_ID or DATE and bring through ACC_NO, along with sums/counts of various data in each table.
If I just bring through the tables I get the the attached (Capture.JPG) which created a synthetic key as I expect. I've read about, but dont fully understand, loosely coupled tables and the Qualify function - can these be used to solve my problem?
Attachment required.jpg shows how I'd liekt he tables to be, but is obviously impossible if I want DATE to be the same field (i.e. I select 01/01/2011 and it counts all TRANS_ID, PLAN_ID and EVENT_ID for each USER_ID relating to that date)
Perumal - your idea worked a treat for linking the tables without causing a loop, however if I try to filter by date there are now 3 date fields instead of 1.
Shivaram - this idea seems to be closer to the money, but am having a little trouble following the INLINE part. I have dabbled with inline for mapping loads, but not as you have used them, can you please elaborate on this a little?
Qlikview Boy! Thanks you very much - I thought this is what I was doing but didnt seem to work, I have replaced with your formula (obviously tweaked to suit the actual DB tables) and it works a dream.
Perumal - I tried your 2nd suggestion and found it works great for the TRANStable, but only pulled through data from PLAN and EVENT where there was a matching USER_ID, Date and ACC_NO, thus restricting the data further than it should have.
Though my problem is now solved, I would like to see the results of Shivaram's suggestion, as the link table idea may prove useful in future.