Joining Multiple Resident tables with Syn keys

asheppardwork — Tue, 23 Jan 2024 19:40:27 GMT

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

Re: Joining Multiple Resident tables with Syn keys

F_B — Mon, 09 Sep 2024 08:23:13 GMT

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.

topic Joining Multiple Resident tables with Syn keys in App Development

Joining Multiple Resident tables with Syn keys

Re: Joining Multiple Resident tables with Syn keys