Joining 4 tables that share 3 fields

[#1]

Hi All,

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)

Thanks for any input you guys might have.

[perumal_41]

Hi

Use This Script

qualify *;

Unqualify USER_ID;

User:

Load USER_ID,

Name

From usertable;

Trans:

Load USER_ID,

Date,

ACC_NO,

TRANS_ID

From transtable;

Plan:

Load USER_ID,

Date,

ACC_NO,

PLAN_ID

From plantable;

Event:

Load USER_ID,

Date,

ACC_NO,

EVENT_ID

From eventtable;

Regards,

Perumal A

[spsrk_84]

Hi ,

Create the Link table using the Commonn fields & create three keys from this table  i.e each key to diff table.

Sample file attached but there is no data ...if the script is not clear to you  let me know..

Regards,

Shivaram

[#4]

Hi Guys,

Thanks for the input, both ideas very helpful.

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?

Thanks

Lee

[Jason_Michaelides · Accepted solution]

This is a very common requirement!  You have 3 fact tables and one dimension table here.  You need to end up with a single fact table and your dimension table.  Try this script:

Fact:

LOAD

     'Trans'              AS     FactType,

     RowNo()     AS     FactID,

     *

FROM Trans...;

CONCATENATE (Fact)

LOAD

     'Plan'     AS     FactType,

     RowNo()     AS     FactID,

     *

FROM Plan...;

CONCATENATE (Fact)

LOAD

     'Event'     AS     FactType,

     RowNo()     AS     FactID,

     *

FROM Event...;

Users:

LOAD

     *

FROM Users...;

That should do it.  If you want to count the different fact types you can use some basic set analysis e.g. COUNT({<FactType='Trans'>} UserID)

Hope this helps,

Jason

[perumal_41]

Hi

Use This Script

User:

Load USER_ID,

Name

From usertable;

Left join(User)

Load USER_ID,

Date,

ACC_NO,

TRANS_ID

From transtable;

Left join(User)

Load USER_ID,

Date,

ACC_NO,

PLAN_ID

From plantable;

Left join(User)

Load USER_ID,

Date,

ACC_NO,

EVENT_ID

From eventtable;

Regards,

Perumal A

[#7]

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.

Thanks all!