How to link two tables by customer and a master calendar with different dates?

[reporting_neu]

Hi,

I have two Tables:

TableA:

Qualify'*';
Unqualify '%*';
Autonumber(Upper(Text(Customer)), '%CUSTOMER') as %CUSTOMER
Autonumber(Date(Datefield), '%MASTERCAL') as %MASTERCAL

TableB:

Qualify '*';

Unqualify '%*';
Autonumber(Upper(Text(Customer)), '%CUSTOMER') as %CUSTOMER
Autonumber(Date(Datefield), '%MASTERCAL') as %MASTERCAL

MASTER_CAL:
Qualify '*';

Datefield
2025-03-03
2025-03-04
2025-03-05
2025-03-06
2025-03-07
2025-03-08

Unqualify '%*';
Autonumber(Date(Datefeld), '%MASTERCAL') as %MASTERCAL

Unfortunately, Qlik always creates a synthetic connection, which means not all values ​​are displayed.

$Syn 1 Table
$Syn1
%MASTERCAL
%CUSTOMER

How can I prevent Qlik from creating a synthetic key so I can link tables A and B using both the customer and the date?

[marcus_sommer]

Both tables contain (mainly) the same. Therefore they shouldn't be linked else being concatenated (union in sql) without any qualifying. To be able to differentiate between them just add an extra source-field to the loads. 

[reporting_neu]

The content is different because Table B contains more details and actually more columns of information. Therefore, I can't simply combine them. The tables I've shown are intended only as examples.

[marcus_sommer]

That the tables may have more or less different columns and/or they contain a different granularity is not mandatory a showstopper for the mentioned approach.

But of course it will depend on the real data and the requirements how to get a suitable data-model. The official recommendation is to develop the data-model in the direction of a star-scheme which means to have a single fact-table + n surrounding dimension-tables by de-normalizing + unifying the field-names and data-structures as much as possible. 

[reporting_neu]

That's basically right.

Table A displays the date, the customer, and a total.

If a customer and a date are selected in Table A, Table B should display the details for that customer and the day. The date can appear multiple times in Table B because the day consists of multiple dates.

Therefore, the tables must communicate with each other about the customer and the date.

[marcus_sommer]

Just for the described usage you wouldn't need the Table A else the selecting against the Table B fields would be enough. And a sum(AmountField) or count(distinct Customer&Date) would also provide commonly measures.

If the total-value is needed for other requirements this total might be just joined back to the details and an access to it may than made with avg() instead a sum() or beside the total amount also a count() is included within the aggregation or ...

[rwunderlich]

As these two dates are effectively different fields, I think you should use the common calendar approach. See Qlikview Cookbook: Tutorial - Using Common Date Dimensions https://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob