Joining Multiple Resident tables with Syn keys

[asheppardwork]

I have a question that I have hit a bit of a wall on.  I am trying to move a calculation from a bar chart to the load statement because of the amount of time it takes to load the page.  I am needing to join multiple resident tables but can't seem to find the right syntax or whatever. 

I need to have the fields from the Report_Table, and each IntervalMatch table to then make a third table; I have tried several different solutions but none create any other tables or they create another $Syn table but they match every other table which is in correct. How can I get what I need?

Code for the Intervals table(s):

 LET varMinDate = Num(MakeDate(2021));                 

 LET varMaxDate = Floor(num(Today()));

 CalendarBuild:

 LOAD

               $(varMinDate) + Iterno()-1 As Num,

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

 Intervals_30:

 Load

               DATE(TempDate-31, 'MM/DD/YYYY') AS From_Date_30
               
               ,DATE(TempDate, 'MM/DD/YYYY') AS To_Date_30
               
               , '30Day_Range' AS IntervalName_30



 Resident CalendarBuild;

 


  //////////////////////////////////


 Intervals_60:

 Load

               DATE(TempDate-60, 'MM/DD/YYYY') AS From_Date_60
               
               ,DATE(TempDate, 'MM/DD/YYYY') AS To_Date_60
               
               , '60Day_Range' AS IntervalName_60



 Resident CalendarBuild;

Code for the Interval Match tables:

[IntervalMatch_30]:

IntervalMatch (Trigger_dt)

Load distinct From_Date_30, To_Date_30 resident Intervals_30;

////////////////////

[IntervalMatch_60]:

IntervalMatch (Trigger_dt)

Load distinct From_Date_60, To_Date_60 resident Intervals_60;

Code from the bar chart that gives me a return rate based on the tables involved:

Returns_Rate_30_Day:
NUM(COUNT( distinct IF( DATE(Trigger_dt,'MM/DD/YYYY') > DATE(From_Date_30,'MM/DD/YYYY') AND DATE(Trigger_dt,'MM/DD/YYYY') < DATE(To_Date_30,'MM/DD/YYYY') AND DATE(Return_Dt,'MM/DD/YYYY') < DATE(To_Date_30,'MM/DD/YYYY') AND DATE(Return_Dt) <> DATE('12/31/1899','MM/DD/YYYY'),RecordNum))/COUNT( distinct IF( DATE(Trigger_dt,'MM/DD/YYYY') > DATE(From_Date_30,'MM/DD/YYYY') AND DATE(Trigger_dt,'MM/DD/YYYY') < DATE(To_Date_30,'MM/DD/YYYY'),RecordNum)), '#,###.0%')

Returns_Rate_60_Day:
NUM(COUNT( distinct IF( DATE(Trigger_dt,'MM/DD/YYYY') > DATE(From_Date_60,'MM/DD/YYYY') AND DATE(Trigger_dt,'MM/DD/YYYY') < DATE(To_Date_60,'MM/DD/YYYY') AND DATE(Return_Dt,'MM/DD/YYYY') < DATE(To_Date_60,'MM/DD/YYYY') AND DATE(Return_Dt) <> DATE('12/31/1899','MM/DD/YYYY'),RecordNum))/COUNT( distinct IF( DATE(Trigger_dt,'MM/DD/YYYY') > DATE(From_Date_60,'MM/DD/YYYY') AND DATE(Trigger_dt,'MM/DD/YYYY') < DATE(To_Date_60,'MM/DD/YYYY'),RecordNum)), '#,###.0%')

proposed data map

[F_B]

Hi @asheppardwork ,

you can combine the interval tables into a single table, with an additional field to differentiate between intervals:

Intervals:
LOAD
From_Date_30 AS From_Date,
To_Date_30 AS To_Date,
'30Day_Range' AS IntervalName
RESIDENT Intervals_30;

CONCATENATE (Intervals)
LOAD
From_Date_60 AS From_Date,
To_Date_60 AS To_Date,
'60Day_Range' AS IntervalName
RESIDENT Intervals_60;

Then, link your trigger dates to the intervals:

IntervalMatch:
IntervalMatch (Trigger_dt)
LOAD DISTINCT
From_Date,
To_Date
RESIDENT Intervals;

And join the Report_Table with the IntervalMatch table:

JOIN (Report_Table)
LOAD
Trigger_dt,
From_Date,
To_Date,
IntervalName
RESIDENT IntervalMatch;

At this point, calculate Returns_Rate directly in the load script by applying the same logic from the bar chart.

Try like this and see if it helps you.