One key with diffrent names. Second key is date

[richards]

The problem :  In table 1 we have the data of the transaction and in table 2 the text. The problem is that I have three customer names on the same customerid. The second key is datefrom and dateto. 

Table 1(Customerid,Amount,filedate)

Table 2(Customerid,Customertext,datefrom,dateto)

The question: How do I connect the correct customertext to the correct transaction? From my point of view the filedate is the transacation date that should represent the customer data at this point. So say if name of id 1 was on 2015 GreenTransport and on 2017 the name was GreenTransport solution. This would for instance be the case (Se table table of solution). I.e in the orginal table I have thousands of dates as for instance 2020-01-11 etc. so it has to be a solutions that check if the filedate is between datefrom and dateto in text table.

[GaryGiles]

In your load script, after Table1 and Table2 have been loaded, add:

Join (Table1)
Load *
Resident Table2;

TableSolution:
Load Customer,
           [Customer name],
           Sum(Amount) as Amount
Resident Table1
where Filedate >= datefrom
     and Filedate <= dateto
Group by Customer, [Customer name];

Drop table Table1, Table2;

[richards]

Dear Gary!

This soulution almost worked for me. Somehow the filedate is impossible to add as an optional dimension in the arc.  I tried to add filedate under the tablesolution and even group by. But this didn´t work. The total script is now accordingly to this

[Table1]:
LOAD
[Customerid],
[Filedate],
[Amount]
FROM xxxxxxxx

[Table2]:
LOAD
Customerid,
datefrom,
dateto,
Customer_name
FROM xxxxxx

Join(Table1)
Load*
Resident Table2;

TableSolution:
Load
[Customerid],
Customer_name,
datefrom,
dateto,

Sum([Amount]) as [Amount]
Resident Table1
where [Filedate]>=datefrom and [Filedate] <=dateto
Group by [Customerid],Customer_name, datefrom, dateto,;
Drop Table Table1,Table2;

On top of this, the above table 1 in the orginal script, has additional 10 column. How do I proceed on this, should these be add in both table1 and tablesolution as dimension and on group by function? If you have a case. Let´s say you add dimension 1+2. How does it work? 

An additional question to this.

On my side the table1 don´t have one column as customerid. Instead it has 4 columns Customerid1,Customerid2. Where should you add the customerid2,3,4 ? I persume this is in tablesolution, the questions is how ?

I made an example of 2 customerid. Could this possible give you an ide of a solution. The issue is to compile customerid1 and 2 from table 2 so that fetch the text, depending on filedate = between from and to date.  For instance the customer id2 could be an head or daughter company.