Join two tables with a third one, BUT NOT BETWEEN THEM on that field(!!)

[#1]

Hello,
i understand that my questions sounds weird so let me explain.

For example, we have a table Payments that includes Date and this Date becomes linked with the Date of my master calendar. Additionally, we have a table invoices that includes Date, but if I keep the same name I get a synthetic table instead because Payments and Invoices are already linked on a supplier ID (!!). So, is there a way to connect Payments Date to Date and invoice Date to Date but NOT Payment Date to Invoice Date on the date field (I still need them to be connected through supplier ID)?? This is only one example, I have many in my script.

Thanks,
Joseph

[#2]

Hi,

Try:

In ur master calender:

Calender Date as [Payment Date] ,

Calender Date as [Invoice Date]

Regards

[#3]

Hi. Thanks for your reponse. I tried that but it doesn't accept the code.

I also updated the description to reflect better what my problem is, take a look.

Thanks

[Gysbert_Wassenaar]

If your two tables are joined on supplier_id then you can't join a master calendar table on both tables. You'll have to use two calendar tables (recommended) or a data island calendar table and use variables and triggers to select a date in both tables.

[#5]

Thanks Gysbert

I will have to try that tomorrow at work. Nonetheless, I have the same problem witha table countries that has the names and coordinates of all countries. This is currently linked to to the payment country and it works fine. When I am trying to load it again as another table from the same excel source (or even a copy of that file) in order to idependently link it with the supplier home_country (different name as well) I get an error that the first field of the excel column was not found! This is bizarre because it loads fine when creating the first countries table (that links to payments) but it doesnt when I want to create the same table as Countries2 in order to link it to supplier home_countries.

Is the situation clear?

[Gysbert_Wassenaar]

If I understand it correctly you have something like this to load the countries from excel:

countries:

load a, b, c

from countries.xls (biff, embedded labels, table is Sheet1$);

and then you try to do a second load with a different field name for the first field:

countries2:

load a1, b, c

from countries.xls (biff, embedded labels, table is Sheet1$);

If you do that I understand why it doens't work. The excel file is still the same, with the same field names. You want to rename the first field so you have to use 'as':

countries2:

load a as a1, b, c

from countries.xls (biff, embedded labels, table is Sheet1$);

[#7]

I'll try that but imagine that it doesn't work even if I load countries2 from a DIFFERENT excel file (copy of the previous one but with another name and different column names...). Does this make sense?

[#8]

Hi,

I solved. I was using the wrong sheet name.

Thanks all