1 Reply Latest reply: Apr 20, 2018 3:17 PM by Felip Drechsler RSS

    Bad Join on Date

    Erica Trotter

      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

        • Re: Bad Join on Date
          Felip Drechsler

          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|42000142000142000
          1|42001142001  
          2|42001 142001
          2|42003242003  
          3|3  
          5|42003 55542003

           

          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?