Iterating through a range of dates

[feedsoal]

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! 

[PhilipMannering]

Hey @feedsoal 

Best way is using the multi-row.

See attached.

P

[feedsoal]

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!

[PhilipMannering]

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]

@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!!