Bad Join on Date

[etrotter]

Hello,

I am experincing an issue with one of my joins, three tables are joined off of Contract&Date and this works for the most part but there are some occurences where there isn't a date in my main table where there is a date in my side tables. So it drops the data from my side table wherever this occurs. I was wondering if there is a way I can build a field with all the contracts along with every possible date in the main table. It doesnt need to be populated but it needs to work to link the other two tables.

Let me know if I can clarify.

thanks,

Erica

[felipedl · Accepted solution]

Hi Erica,

In my opinion, if you do all dates per contract, it will multiply your data by a lot for each individual contract, even though that's not in the main table.

Over time that will start consuming a lot of disk space and memory, if you're doing a historic view.

Which specific need do you have for this case where there's a contract but no date associated to it?

If you put the values on a table, with the #Key being shown as a field, you'll just see the contract portion on the table, with the rest of the data empty.

Let's say you have this data:

Contract:

Load

Id & '|' & Date as #Key,

Id as ContractId,

Date as Contract.Date;

Load * Inline

[

Id,Date

1,42000

1,42001

2,42003

3,

];

Table2:

Load

ContractId & '|' & Date as #Key,

Id as Table2Id,

Date as Table2.Date;

Load * Inline

[

Id,Date,ContractId

1,42000,1

1,42001,2

555,42003,5

];

It will give you the following result:

#Key	ContractId	Contract.Date	Table2Id	Table2.Date
1|42000	1	42000	1	42000
1|42001	1	42001
2|42001			1	42001
2|42003	2	42003
3|	3
5|42003			555	42003

Both tables are linked, but the second table "Table2" doesn't have a correspondent in contract table.

Can you add more to what you need?