Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Ernests
Partner - Creator
Partner - Creator

Two tables linked on id but each table has its own date.

Hi,

I have 2 tables linked on article code but for each table there are separate date fields (YYYY-MM). As the result I need pivot table where there will be one measure from each table distributed by date (YYYY-MM). Now if I choose one of the dates from these tables, it show measure correctly only for measure which comes from corresponding table.

Maybe I can use set analysis to somehow unify these dates?

Thanks.

 

Labels (6)
6 Replies
Mark_Little
Luminary
Luminary

Hi, 

I think you would need to address this in the date model and script. Have a read of this it should point you in the right direction.

https://community.qlik.com/t5/Design/Canonical-Date/ba-p/1463578

 

Ernests
Partner - Creator
Partner - Creator
Author

Thanks, I got the idea but in my case there are 2 source tables.

For example, in sales table in 2023-01 measure value is 100 but in purchase table 2023-02 value is 50. That means that none of these tables include dates without values. And note that these tables are already linked on id's so I can't just simply join on date.

Sebastian_Dec
Creator II
Creator II

From what you wrote, I understand that your connection looks something like this?

sales:
LOAD 
art_id,
date_sales 
FROM ...;

purchase:
LOAD 
art_id,
date_purchase
FROM ...;

 

And you can't do something like this:

sales:
LOAD 
art_id,
date_sales as date 
FROM ...;

purchase:
LOAD 
art_id,
date_purchase as date
FROM ...;

 or create a key like this:

sales:
LOAD 
art_id & date_sales as key
art_id as art_id_sales,
date_sales 
FROM ...;

purchase:
LOAD 
art_id & date_purchase as key
art_id as art_id_purchase,
date_purchase
FROM ...;
Thanks & Regards,
Please close the thread by marking correct answer & give likes if you like the post.
Ernests
Partner - Creator
Partner - Creator
Author

Thank you for reply, yes, something like this. 

In first script you wrote I have different table sources so the columns are not the same, only some values are same.

In second script I cannot do that because of synth keys. It is already linked on art_id.

In third script it won't work because the art_id may differ between these tables, for example, in sales there will be art_id 100, 200, 300 but in purchase only art_id 300. The same with dates.

Sebastian_Dec
Creator II
Creator II

Ok, in this case I can suggest adding an additional table that will connect art_id from purchase to art_it sales (id dictionary table).

Something like this:

art_id_sales art_id_purchase
100 300
200 300
300 300

 

You can also give some qvd files as examples.

Thanks & Regards,
Please close the thread by marking correct answer & give likes if you like the post.
marcus_sommer

Instead of linking the two fact-tables just concatenate them by harmonizing the field-names and data-structures and adding an extra field Source to be able to differentiate between them within the UI.