Iterating through a range of dates

Question

Hello!

I'm new to Alteryx and I'm translating some code from ACL Analytics to Alteryx workflows.

Here is the issue:

I need to iterate over a range of dates to perform some tests with given conditions, and I'm pretty confused on how exactly I can do it with iterative macros.

I wrote some piece of code using R just to represent the kind of iteration I need.

library(lubridate)

# User Input
start_date <- ym("201401")

# User Input
end_date <- ym("202009")

# Variables
var_date <- add_with_rollback(start_date,months(-1))
var_date_end <- end_date

# While Loop
while(var_date < var_date_end){
  var_date <- add_with_rollback(var_date,months(1))
  print(var_date)
}

And this is the output:

[1] "2014-01-01"
[1] "2014-02-01"
[1] "2014-03-01"
[1] "2014-04-01"
[1] "2014-05-01"
...
[1] "2020-06-01"
[1] "2020-07-01"
[1] "2020-08-01"
[1] "2020-09-01"

Basically, I just want to guarantee that in each iteration, my tests will be performed considering 201401 first, then 201402, until 202009. Is iterative macro the way? How?

Unfortunately I couldn't find the help I need with Alteryx videos only.

Additionally, I could "almost" find a way using generate row tool, but it would turn my dataset with 7,000,000 lines to a monstrous dataset with 500,000,000+ lines.

Thank you!

feedsoal · Answer

@PhilipMannering, the piece of code was just to represent the kind of loop I expect to be able to do in Alteryx. I'll show you an example of what I need to do (ACL Script). SET SAFETY OFF COM ###### CLAIMS DATA LOADED PREVIOUSLY ###### COM ###### IT CONTAINS DATA ABOUT SEVERAL TYPES OF INSURANCE CLAIMS AND I WANT TO LOOK AT SOME OBSERVED VALUES IN EACH DATE ###### CLOSE CLOSE SEC COM ###### DEFINING THE DATES FOR CONSISTENCY TESTS ######## INITIAL_MONTH = "01" INITIAL_YEAR = "2014" FINAL_MONTH = "09" FINAL_YEAR = "2020" VAR_MONTH_START = VAL(INITIAL_MONTH;0) VAR_YEAR_START = VAL(INITIAL_YEAR;0) VAR_MONTH_END = VAL(FINAL_MONTH;0) VAR_YEAR_END = VAL(FINAL_YEAR;0) VARDATA = STRING(VAR_YEAR_START;4) + ZONED(VAR_MONTH_START;2) VARDATAF = STRING(VAR_YEAR_END;4) + ZONED(VAR_MONTH_END;2) OPEN CLAIMS_DATA EXTRACT FIELDS OCCURR_DATE REPORT_DATE REF_DATE INDEM_WO_FINAN_EXP FINAN_EXP CLAIM_TYPE MOV_TYPE LINE REINS_FLAG RECOVER_FLAG ADM_JUD_FLAG MOV_VALUE MONETARY_VALUE CLAIM_NUMBER TO CLAIMS_TEST CLOSE DO SCRIPT TESTS while VAL(VARDATA;0) <= VAL(VARDATAF;0) COM ##### EXAMPLE OF A TEST ##### OPEN CLAIMS_TEST DEL GROSS_OBSERVED_VALUE OK DEFINE GROSS_OBSERVED_VALUE COMPUTED INDEM_WO_FINAN_EXP IF (VAL(OCURR_DATE;0) <= %VARDATA% AND VAL(REPORT_DATE;0) > %VARDATA%) AND (VAL(MOV_TYPE;0)=1 OR VAL(MOV_TYPE;0)=2 OR VAL(MOV_TYPE;0)=5 OR VAL(MOV_TYPE;0)=6) AND VAL(REINS_FLAG;0) = 0 AND VAL(RECOVER_FLAG;0) = 0 0,00 DEL REINSURED_OBSERVED_VALUE OK DEFINE FIELD REINSURED_OBSERVED_VALUE COMPUTED INDEM_WO_FINAN_EXP IF (VAL(OCURR_DATE;0) <= %VARDATA% AND VAL(REPORT_DATE;0) > %VARDATA%) AND (VAL(MOV_TYPE;0)=1 OR VAL(MOV_TYPE;0)=2 OR VAL(MOV_TYPE;0)=5 OR VAL(MOV_TYPE;0)=6) AND VAL(REINS_FLAG;0) = 1 AND VAL(RECOVER_FLAG;0) = 0 0,00 DEL NET_OBSERVED_VALUE OK DEFINE FIELD NET_OBSERVED_VALUE COMPUTED INDEM_WO_FINAN_EXP IF (VAL(OCURR_DATE;0) <= %VARDATA% AND VAL(REPORT_DATE;0) > %VARDATA%) AND (VAL(MOV_TYPE;0)=1 OR VAL(MOV_TYPE;0)=2 OR VAL(MOV_TYPE;0)=5 OR VAL(MOV_TYPE;0)=6) AND VAL(RECOVER_FLAG;0) = 0 0,00 SUMMARIZE ON "%VARDATA%" AS "BASE_DATE" LINE SUBTOTAL GROSS_OBSERVED_VALUE REINSURED_OBSERVED_VALUE NET_OBSERVED_VALUE TO OBSERVED_CLAIMS PRESORT Thank you!!

philipmannering · Answer

Sorry @feedsoal. I don't understand what you're asking. If there's a value for the initial date, it will get replicated (the same) for all the generated dates. But I don't understand where this value comparison comes in. You don't do this in your code. Perhaps you have an example?

feedsoal · Answer

Thank you for the reply!

Well, I notice how the flow works in your example. But, how it will be stored in my "var_date" variable until the process is complete?

For example, I have two columns, one with date and one with a value. What I want to do is check if all the values in my current date, say 201501, are greater than 100. Then I'll check it for 201502, 201503, etc. Is it a possibility with the multi-row?

Thanks!