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

[Ernests]

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.

[Mark_Little]

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]

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]

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 ...;

[Ernests]

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]

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.

[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.