Data extract every Monday

Question

Good afternoon, Alteryx community.

Good afternoon, Alteryx team.

This is a picture of part of my workflow.  IHP.txt is my primary input file (loaded every day) with many different PRGM_NA values (approximately 50).  I want to extract 4 of those program name values every Monday (one of the extracted values is the one shown) and write them to 4 separate data files.  How do I do that, please?

This is the Access DB VBA functionality that I am looking to replicate in Alteryx.

If Weekday(Date) = 2 Then

' export the automatic Monday query results

DoCmd.RunSavedImportExport "Export-qsel_Sally_All_Fields"

DoCmd.RunSavedImportExport "Export-qsel_AVAI_AINC_All_Fields"

DoCmd.RunSavedImportExport "Export-qsel_PUAA_PPEL_Initial"

DoCmd.RunSavedImportExport "Export-qsel_RTRN_NewHire_All_Fields"

' open Excel

Call GetExcel

MsgBox "Automatic query results exported"

End If

Thank you very much for your help.

Sincerely,

Errol C. Isenberg

Data Analyst

954-214-9406

Errol.isenberg@deo.myflorida.com

errolcisenberg@gmail.com

Errol · Answer

Good morning, Alteryx community.

I posed a question yesterday looking for some help with some automated processing, and it was suggested that some additional information would be useful, so here it is.

I am currently running an Access DB that links to a large file (titled IHP.txt) of about 5M records every weekday.  The business case requires that certain segments of the IHP.txt file be sent to management every Monday.

Our IT department wants me to replicate in Alteryx what I am doing in Access.  The primary Access DB interface is shown below.  What happens on a Monday is that when I click the GET FILES button to link to IHP.txt, the appropriate queries to extract the necessary segments are run (see IF statement below screenshot).  The queries export the segments as .xlsx files, which are then processed and distributed by an Excel VBA program.

What I am interested in learning is how to replicate this functionality in my Alteryx workflow, and I thank you for any help you might be able to provide.

I have attached small samples of the input IHP.txt file (100K records) and two of the four output files, as well as the full Access VBA code (IHP_Linked DB VBA.pdf) and the Excel program (Excel VBA.pdf) that processes the exported query results.  The SQL statements for the 4 queries used are shown after the IF statement.

If Weekday(Date) = 2 Then

' export the automatic Monday query results

DoCmd.RunSavedImportExport "Export-qsel_Sally_All_Fields"

DoCmd.RunSavedImportExport "Export-qsel_AVAI_AINC_All_Fields"

DoCmd.RunSavedImportExport "Export-qsel_PUAA_PPEL_Initial"

DoCmd.RunSavedImportExport "Export-qsel_RTRN_NewHire_All_Fields"

' open Excel

Call GetExcel

MsgBox "Automatic query results exported"

End If

qsel_Sally_All_Fields

SELECT tbl_IHP.CLAIMANT_ID, tbl_IHP.CLAIM_ID, tbl_IHP.ISSUE_ID, tbl_IHP.CLAIM_STATUS_CD, tbl_IHP.MON_STATUS_CD, tbl_IHP.SOURCE_CD, tbl_IHP.ACTIVE_IN, tbl_IHP.LEVEL_CD, tbl_IHP.START_DT, tbl_IHP.END_DT, tbl_IHP.HOLD_PMT_IN, tbl_IHP.TYPE_CD, tbl_IHP.SUB_TYPE_CD, tbl_IHP.ADJCTR_ID, tbl_IHP.WEEK_REQUEST, tbl_IHP.DIRECT_DEPOSIT_CD, tbl_IHP.BENEFIT_YEAR_BEGIN_DT, tbl_IHP.FIRST_PAY_DT, tbl_IHP.TIMELY_FIRST_PAY, tbl_IHP.CREATE_DT, tbl_IHP.CLMT_LOCK, tbl_IHP.CLM_LOCK, tbl_IHP.DETECTION_DT, tbl_IHP.FILTER_EXCLUSION_CD, tbl_IHP.BUSINESS_AREA_CD, tbl_IHP.SOURCE_GROUP_CD, tbl_IHP.END_WEEK_DATE, tbl_IHP.GROUP_CD, tbl_IHP.SIDES_ISSUE_INDICATOR, tbl_IHP.MAX_EMPLOYER_FF_DUE_DT, tbl_IHP.MAX_CLAIMANT_FF_DUE_DT, tbl_IHP.EMPLOYER_FF_RECEIVED_IN, tbl_IHP.CLAIMANT_FF_RECEIVED_IN, tbl_IHP.PROGRAM_ID, tbl_IHP.DISASTER_UNEMPLOYMENT_IN, tbl_IHP.PRGM_NA, tbl_IHP.EMPR_ACCT_ID, tbl_IHP.ISSUE_ON_HOLD, tbl_IHP.NUMBER_OF_DAYS, tbl_IHP.COVID19
FROM tbl_IHP
WHERE (((tbl_IHP.PRGM_NA) = "DSTR - Hurricane Sally"));

qsel_AVAI_AINC_All_Fields

SELECT tbl_IHP.CLAIMANT_ID, tbl_IHP.CLAIM_ID, tbl_IHP.ISSUE_ID, tbl_IHP.CLAIM_STATUS_CD, tbl_IHP.MON_STATUS_CD, tbl_IHP.SOURCE_CD, tbl_IHP.ACTIVE_IN, tbl_IHP.LEVEL_CD, tbl_IHP.START_DT, tbl_IHP.END_DT, tbl_IHP.HOLD_PMT_IN, tbl_IHP.TYPE_CD, tbl_IHP.SUB_TYPE_CD, tbl_IHP.ADJCTR_ID, tbl_IHP.WEEK_REQUEST, tbl_IHP.DIRECT_DEPOSIT_CD, tbl_IHP.BENEFIT_YEAR_BEGIN_DT, tbl_IHP.FIRST_PAY_DT, tbl_IHP.TIMELY_FIRST_PAY, tbl_IHP.CREATE_DT, tbl_IHP.CLMT_LOCK, tbl_IHP.CLM_LOCK, tbl_IHP.DETECTION_DT, tbl_IHP.FILTER_EXCLUSION_CD, tbl_IHP.BUSINESS_AREA_CD, tbl_IHP.SOURCE_GROUP_CD, tbl_IHP.END_WEEK_DATE, tbl_IHP.GROUP_CD, tbl_IHP.SIDES_ISSUE_INDICATOR, tbl_IHP.MAX_EMPLOYER_FF_DUE_DT, tbl_IHP.MAX_CLAIMANT_FF_DUE_DT, tbl_IHP.EMPLOYER_FF_RECEIVED_IN, tbl_IHP.CLAIMANT_FF_RECEIVED_IN, tbl_IHP.PROGRAM_ID, tbl_IHP.DISASTER_UNEMPLOYMENT_IN, tbl_IHP.PRGM_NA, tbl_IHP.EMPR_ACCT_ID, tbl_IHP.ISSUE_ON_HOLD, tbl_IHP.NUMBER_OF_DAYS, tbl_IHP.COVID19
FROM tbl_IHP
WHERE (((tbl_IHP.TYPE_CD)="AVAI") AND ((tbl_IHP.SUB_TYPE_CD)="AINC"));

qsel_PUAA_PPEL_Initial

SELECT tbl_IHP.CLAIMANT_ID, tbl_IHP.CLAIM_ID, tbl_IHP.ISSUE_ID, tbl_IHP.CLAIM_STATUS_CD, tbl_IHP.MON_STATUS_CD, tbl_IHP.SOURCE_CD, tbl_IHP.ACTIVE_IN, tbl_IHP.LEVEL_CD, tbl_IHP.START_DT, tbl_IHP.END_DT, tbl_IHP.HOLD_PMT_IN, tbl_IHP.TYPE_CD, tbl_IHP.SUB_TYPE_CD, tbl_IHP.ADJCTR_ID, tbl_IHP.WEEK_REQUEST, tbl_IHP.DIRECT_DEPOSIT_CD, tbl_IHP.BENEFIT_YEAR_BEGIN_DT, tbl_IHP.FIRST_PAY_DT, tbl_IHP.TIMELY_FIRST_PAY, tbl_IHP.CREATE_DT, tbl_IHP.CLMT_LOCK, tbl_IHP.CLM_LOCK, tbl_IHP.DETECTION_DT, tbl_IHP.FILTER_EXCLUSION_CD, tbl_IHP.BUSINESS_AREA_CD, tbl_IHP.SOURCE_GROUP_CD, tbl_IHP.END_WEEK_DATE, tbl_IHP.GROUP_CD, tbl_IHP.SIDES_ISSUE_INDICATOR, tbl_IHP.MAX_EMPLOYER_FF_DUE_DT, tbl_IHP.MAX_CLAIMANT_FF_DUE_DT, tbl_IHP.EMPLOYER_FF_RECEIVED_IN, tbl_IHP.CLAIMANT_FF_RECEIVED_IN, tbl_IHP.PROGRAM_ID, tbl_IHP.DISASTER_UNEMPLOYMENT_IN, tbl_IHP.PRGM_NA, tbl_IHP.EMPR_ACCT_ID, tbl_IHP.ISSUE_ON_HOLD, tbl_IHP.NUMBER_OF_DAYS, tbl_IHP.COVID19
FROM tbl_IHP
WHERE (((tbl_IHP.TYPE_CD)="PUAA") AND ((tbl_IHP.SUB_TYPE_CD)="PPEL") AND ((tbl_IHP.SOURCE_GROUP_CD)="Initial"));

qsel_RTRN_NewHire_All_Fields

SELECT tbl_IHP.CLAIMANT_ID, tbl_IHP.CLAIM_ID, tbl_IHP.ISSUE_ID, tbl_IHP.CLAIM_STATUS_CD, tbl_IHP.MON_STATUS_CD, tbl_IHP.SOURCE_CD, tbl_IHP.ACTIVE_IN, tbl_IHP.LEVEL_CD, tbl_IHP.START_DT, tbl_IHP.END_DT, tbl_IHP.HOLD_PMT_IN, tbl_IHP.TYPE_CD, tbl_IHP.SUB_TYPE_CD, tbl_IHP.ADJCTR_ID, tbl_IHP.WEEK_REQUEST, tbl_IHP.DIRECT_DEPOSIT_CD, tbl_IHP.BENEFIT_YEAR_BEGIN_DT, tbl_IHP.FIRST_PAY_DT, tbl_IHP.TIMELY_FIRST_PAY, tbl_IHP.CREATE_DT, tbl_IHP.CLMT_LOCK, tbl_IHP.CLM_LOCK, tbl_IHP.DETECTION_DT, tbl_IHP.FILTER_EXCLUSION_CD, tbl_IHP.BUSINESS_AREA_CD, tbl_IHP.SOURCE_GROUP_CD, tbl_IHP.END_WEEK_DATE, tbl_IHP.GROUP_CD, tbl_IHP.SIDES_ISSUE_INDICATOR, tbl_IHP.MAX_EMPLOYER_FF_DUE_DT, tbl_IHP.MAX_CLAIMANT_FF_DUE_DT, tbl_IHP.EMPLOYER_FF_RECEIVED_IN, tbl_IHP.CLAIMANT_FF_RECEIVED_IN, tbl_IHP.PROGRAM_ID, tbl_IHP.DISASTER_UNEMPLOYMENT_IN, tbl_IHP.PRGM_NA, tbl_IHP.EMPR_ACCT_ID, tbl_IHP.ISSUE_ON_HOLD, tbl_IHP.NUMBER_OF_DAYS, tbl_IHP.COVID19
FROM tbl_IHP
WHERE (((tbl_IHP.TYPE_CD)="RTRN") AND ((tbl_IHP.BUSINESS_AREA_CD)="BPC"));

qsel_Sample_Incarceration.xlsx

IHP_Linked DB VBA.pdf

Excel VBA.pdf

qsel_100K.xlsx

qsel_Sample_Sally.xlsx

Qiu · Answer

@Errol

If only filter the data timestamped with Monday, it should be easy.

I am wondering the access function you are using, do you want the same thing to be performed by alteryx?

It will help us to understand the request better if you could give a sample of "IHP.txt" and desired output.

vizAlter · Answer

@Errol — That means you just want all the records from this new filter if day is Monday. Try the updated workflow.

Data extract every Monday__vA.yxmd