Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Joining 4 tables that share 3 fields

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.

1 Solution

Accepted Solutions
jason_michaelid
Honored Contributor II

Re: Joining 4 tables that share 3 fields

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

6 Replies
perumal_41
Valued Contributor II

Joining 4 tables that share 3 fields

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
Contributor III

Re: Joining 4 tables that share 3 fields

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

Not applicable

Joining 4 tables that share 3 fields

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_michaelid
Honored Contributor II

Re: Joining 4 tables that share 3 fields

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
Valued Contributor II

Joining 4 tables that share 3 fields

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

Not applicable

Joining 4 tables that share 3 fields

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!

Community Browser