topic Re: Calendar doesn't link to facts table in App Development

Calendar doesn't link to facts table

bmenicucci — Thu, 16 Apr 2020 17:06:51 GMT

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

Re: Calendar doesn't link to facts table

Vegar — Thu, 16 Apr 2020 21:15:59 GMT

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

Re: Calendar doesn't link to facts table

bmenicucci — Thu, 16 Apr 2020 22:01:09 GMT

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

Re: Calendar doesn't link to facts table

Vegar — Fri, 17 Apr 2020 07:00:45 GMT

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?

Re: Calendar doesn't link to facts table

bmenicucci — Fri, 17 Apr 2020 08:45:48 GMT

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!

Re: Calendar doesn't link to facts table

lorenzoconforti — Fri, 17 Apr 2020 09:46:33 GMT

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;

Re: Calendar doesn't link to facts table

lorenzoconforti — Fri, 17 Apr 2020 09:50:25 GMT

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

Re: Calendar doesn't link to facts table

bmenicucci — Fri, 17 Apr 2020 12:04:58 GMT

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!

Re: Calendar doesn't link to facts table

Vegar — Sat, 18 Apr 2020 06:43:27 GMT

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.

Re: Calendar doesn't link to facts table

bmenicucci — Sat, 18 Apr 2020 08:08:41 GMT

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

Best

Brunello