Reference a resident table field in a do while loop while calculating date based percentages

[asheppardwork]

I have a a need to iterate through a resident table making calculations that will go into another table. Yet, anytime I try to add a field from the resident table to my do while loop I get 'invalid calculation' with no other hints.

I have a resident table like this:

RecNo, Trigger_Date, Return_Date, Item, Dept

I need to get three things

1) the number of records with trigger and return dates between vStartDateGA and vEndDateGA for a 30Day_Returns count.

      1a the vStartDateGA and vEndDateGA will be calculated for every calendar date for 3 years.

2) the number of records with just trigger dates between vStartDateGA and vEndDateGA for a 30Day_Totals count.

3) the percentage of 30Day_Returns/30Day_Totals to get a 30Day_Rate.

Psuedo code:

Count the number of distinct records when the Trigger Date is between 30 days prior to the 'Rate Calc Date' and is not after the 'Rate Calc Date' and the Return Date does not equal '12/31/1899'
then divide by the
Count of distinct records where the Trigger Date is between 30 days prior to the 'Rate Calc Date' and is not after the 'Rate Calc Date'

Return this as a 30Day_Rate of return percentage

I am looping through to do the calculations -

[Rate_Calendar_Dept]:

SET DateFormat='YYYY-MM-DD';

LET vRateCalcDate = '2023-01-01';

LET vStartDateGA = '2023-01-01';

LET vEndDateGA = '2023-03-31';



do while vStartDateGA <= vEndDateGA

   Let vRateCalcDate = date('$(vStartDateGA)','YYYY-MM-DD');
   
   Load
   
   '$(vRateCalcDate)' as Rate_date,
   NUM(Count( distinct IF( [Trigger_dt] >= Date('$(vRateCalcDate)'-30) AND [Trigger_dt] <= Date('$(vRateCalcDate)') AND DATE(Return_Dt) <= DATE('$(vRateCalcDate)') AND DATE(Return_Dt) <> DATE('12/31/1899','MM/DD/YYYY'), RecordNum,0) )/Count( distinct IF( [Trigger_dt] >= Date('$(vRateCalcDate)'-30) AND [Trigger_dt] <= Date('$(vRateCalcDate)'), RecordNum,0) ), '#,###.0%') as '30Day_Rate',
   Count( distinct IF( [Trigger_dt] >= Date('$(vRateCalcDate)'-30) AND [Trigger_dt] <= Date('$(vRateCalcDate)') AND DATE(Return_Dt) <= DATE('$(vRateCalcDate)') AND DATE(Return_Dt) <> DATE('12/31/1899','MM/DD/YYYY'), RecordNum,0) ) as '30Day_Returns',
   Count( distinct IF( [Trigger_dt] >= Date('$(vRateCalcDate)'-30) AND [Trigger_dt] <= Date('$(vRateCalcDate)'), RecordNum,0) ) as '30Day_Totals'
    
   Resident Report_Table;

   let vStartDateGA = date('$(vStartDateGA)'+1,'YYYY-MM-DD');

loop  

When I add any other field to the statement after Load I get the 'Invalid calcuation' error; how can I add Trigger_Date and Dept into my Load statement?

It should come out looking like this:

Dept, Trigger_Date, Rate_Date, 30Day_Rate, 30Day_Returns, 30Day_Totals
Sales, 2023-01-01, 2023-01-01, 58.5%, 1696, 2901
Legal, 2023-01-01, 2023-01-01, 57.5%, 1609, 2800
AP, 2023-01-01, 2023-01-02, 58.4%, 1630, 2790
IT, 2023-01-01, 2023-01-02, 59.4%, 1737, 2926

[marcus_sommer · Accepted solution]

You are applying an aggregation-load. Each field which isn't included within aggregation-function needs to be listed within the group by section and this has of course an impact an the aggregation-results. It's not seldom that the intended aggregations aren't possible against the source-table. If so the aggregations needs to be performed separately and afterwards merged again with the source. It will depend on your data and requirements what's possible respectively needed.

Beside of this your approach with using outside-loops to apply n aggregation-loads against a source will be quite resource-consuming. Even by rather small data-sets like 1 M of records and the 60 iterations from your above time-frame it will run quite a while especially as no where-clauses are applied and multiple if-loops as conditions are used.

I could imagine that it might be solvable without any aggregation or that the approach could be optimized. Optimized means to transfer some or maybe all if-loop conditions into the where-clauses to skip records which are in general not regarded. If the conditions of multiple calculations are not completely compatible with each other just use two or three loads to fetch the relevant sub-set of data. The next step may be not to compare the date fields/variables against each other else to subtract them - maybe within a sign() or a class() to create flag- and/or cluster-results from them.

On top of it may come further evaluations in the script or it might be already sufficient to calculate the final results within the UI.  

[asheppardwork]

This is a first time for me to do this kind of calculation.  I added 

Resident Report_Table group by Dept;

and it was able to finish, but as you said it really bloated the dataset from the original source of 82k records to over 2.8 million.  Here is a mock-up of the data in the source, do you have a better approach that you could suggest? 

RecNo,Trigger_Date,Return_Date,Item,EEName,Dept
101,4/15/2021,5/10/2021,454547,John Doe,Legal
102,4/15/2021,5/6/2021,454797,Jane Dough,IT
103,4/15/2021,6/7/2021,455047,Doug Funny,Sales
104,10/4/2021,,455297,Han Solo,Security
129,10/19/2021,11/18/2021,461547,Jimmy Klark,Legal
105,11/22/2021,,455547,Din Dejarin,Legal
106,12/7/2021,1/15/2022,455797,Greedo,IT
107,2/1/2022,2/23/2022,456047,Boba Fett,Sales
108,2/1/2022,9/6/2023,456297,Jabba Hutt,Security
109,2/1/2022,8/16/2022,456547,Sea Threepio,Legal
110,4/21/2022,9/14/2022,456797,Arty Detoo,IT
111,7/13/2022,,457047,Rea Palpatine,Sales
112,7/19/2022,8/10/2022,457297,Ben Solo,Security
113,8/18/2022,8/30/2022,457547,Bruce Wayne,Legal
114,10/19/2022,11/2/2022,457797,Lance Calrusian,IT
115,10/28/2022,11/14/2022,458047,Ralph Tuttle,Sales
116,1/6/2023,1/19/2023,458297,Leo Tuttle,Security
117,1/6/2023,2/7/2023,458547,Mike Tuttle,Legal
118,1/19/2023,,458797,Donny Tuttle,IT
119,2/4/2023,,459047,Zoe Spengler,Sales
120,2/4/2023,,459297,Winston Zeddemore,Security
121,3/20/2023,,459547,Ray Stanz,Legal
122,3/20/2023,4/11/2023,459797,Egon Spengler,IT
123,8/16/2023,,460047,Peter Venkman,Sales
124,8/16/2023,,460297,Lucas Sky,Security
125,8/16/2023,9/9/2023,460547,Leia Sky,Legal
126,9/12/2023,,460797,Anton Stark,IT
127,9/19/2023,10/27/2023,461047,Chewy Bakka,Sales
128,9/19/2023,,461297,YoYo Dah,Security

[asheppardwork]

So as to not clutter up this thread I'll post a more generic version of this question to see about re-doing the calculation process as @marcus_sommer makes a very valid point about the data bloat.  I just don't know how else to go about it.

[asheppardwork]

the new question is here if you want to help.