Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
josecanalla
Creator
Creator

Filter by date when two tables are linked

Hello, I have two tables "sales" and "purchases" linked between the product.

Also, the two tables have a "date" field. I want to filter two tables at same time in function of same "date". I can't link tables using date field, because I get a circular reference error.

What can I do? Thanks!

1 Solution

Accepted Solutions
Quy_Nguyen
Specialist
Specialist

As my understanding: SalesOrder and SalesOrderLine (order detail) are linked by SalesOrrderID. (same principle for Purchases).

Base on your business, you can have 2 ways:

1. Combine SalesOrder and SalesOrderLine into 1 table (by Join or ApplyMap). After that you can follow the model above.


2. Bring Date field from SalesOrder to SalesOrderLine by using ApplyMap. Then delete the Date field in the SalesOrder. And your model will be like this:

Capture.PNG

View solution in original post

9 Replies
MK9885
Master II
Master II

How does the data model look?

Can you share screen shot?

josecanalla
Creator
Creator
Author

Yes, but it is in spanish.

For simplicity:

  • Table "sales" have "product_id" which references "products" table and "sale_date".
  • Table "purchases" have "product_id" which references "products" table and "purchase_date".

For example I want to select "november 2017" and get all sales and purchases for this month.

Anonymous
Not applicable

Hi Jose pls go thru below 2 links. Iwas facing similar challenges. hope it helps. Thanks

How to Create a Master Calendar with 3 Fact Tables

How to associate 2 tables with diff dates

Quy_Nguyen
Specialist
Specialist

Hi Jose,

May be you should design your data model like this:

Capture.PNG

CompositeKey is created from ProductCode & Date.

You can view the demo script in attached qvf. !

shraddha_g
Partner - Master III
Partner - Master III

you can link those tables using Link Table and Composite Key.

Refer Combination of 2 fields to link 2 tables

luismadriz
Specialist
Specialist

I also find this post from hic very useful: Canonical Date

Cheers,

Luis

josecanalla
Creator
Creator
Author

It's a good idea. How can I implement it with a PurchaseOrderLine table and SaleOrderLine table?

The "Sales" and "Purchases" table have the Date, and the "...OrderLine" tables have the ProductID.

Quy_Nguyen
Specialist
Specialist

As my understanding: SalesOrder and SalesOrderLine (order detail) are linked by SalesOrrderID. (same principle for Purchases).

Base on your business, you can have 2 ways:

1. Combine SalesOrder and SalesOrderLine into 1 table (by Join or ApplyMap). After that you can follow the model above.


2. Bring Date field from SalesOrder to SalesOrderLine by using ApplyMap. Then delete the Date field in the SalesOrder. And your model will be like this:

Capture.PNG

michael_anywar
Creator
Creator

HI, I have similar problem, however with 3 tables

Sales, Order and Stock level.

Will be glad if i can get some help

Help with joining multiple tables while comparing dates (Date In)