Calendar doesn't link to facts table

[bmenicucci]

Hello experts,

I need your help as I don't understand why the heck these two tables are not linking correctly. I have a facts table which I'd like to link to Calendar but it looks like something prevent it to happens.😵

Enclosed you'll find a sample application. Any suggestion or hint will be highly appreciated!

Thanks, Brunello

[Vegar]

It looks like the transaction an calendar are connected. it's just not connected on all dates. 

[bmenicucci]

Hello Vegar,
Thanks for your promptness. However, it *looks* they're connected but if
you check carefully you will see that records from table KPILavorazioni
gets null for every field from Calendar except KeyCalendar.
It gets me crazy...!
Have a nice time ahead
Brunello

[Vegar]

Thanks for pointing that out @bmenicucci . I did a second look at your application. Could it be the use of  that is causing your trouble?

CALL Qvc.LinkTable('LinkTable','Calendar','KeyCalendar');
CALL Qvc.LinkTable('LinkTable','KPILavorazioni','KeyKPILav,KeyCalendar');

You are linking one field from Calendar to two fields from KPILavorazioni. Correct me if I'm wrong but by the look of it you don't need to use Qvc.LinkTable at all? 

[bmenicucci]

Hi Vegar,
actually, as you can see in the "Link" tab the tables to load are more than
these two. However, in this case it seems I'm not able to make the two link
correctly and I don't understand why. I've tried to left join KeyCalendar
in table KPILavorazioni through a different field (_DateSerial), use floor
function, trim any leading or tail spaces but nothing succeeded while all
other tables looks linked correctly.
The purpose of connecting these two is to make calculations for different
periods such as months, weeks or quarters.
Any suggestions will really help
Thanks!

[lorenzoconforti]

There seems to be an issue with the %LinkTable_Key in the Calendar table; if you drop and create a new KeyCalendar field in the Calendar table the two will be linked correctly:

drop Field %LinkTable_Key from Calendar;

Calendar2:
load *, YearMonth & num(Day,'00') as KeyCalendar resident Calendar;

drop table Calendar;

[lorenzoconforti]

This, of course, won't necessarily solve the problem you have but should point out to where the issue is

[bmenicucci]

Hello Lorenzo,

could you please elaborate on this? Don't see what the problem is and how to try implement your suggestion...
and by the way, feel free to contact me - we could better explain in italian 🙂 

Thanks!

[Vegar · Accepted solution]

I see your other qvc.linktable calls as well, but I think you need to rethink this approach. In order for the SUB to work and link correctly the  tables you are linking needs to use qvc.LinkTable with the same field names.

Consider qvc.LinkTable as an way of eliminating synthetic keys between two or more tables that have the same common fields.

[bmenicucci]

Thanks Vegar! I misunderstood how to use that routine and thought I could use as if manually creating a Link Table.

Best

Brunello