Data extract every Monday

[Errol]

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

[vizAlter]

Hi @Errol — Use a Filter tool and write this expression in the Custom Filter:

DateTimeDiff([myDate],DateTimeToday(),"day") = 0 AND
IIF(DateTimeFormat([myDate],"%A") = "Monday", 1, 0)

Then, add your the "Output" tool and configure them to export the results.

Let me know if you have any query.

[Errol]

Hi, vizAlter. 

Thank you for the response.  Unfortunately, when I typed this into the Custom filter, i get this error message:  Parse error at char (14): Unknown variable "myDate".  I don't think I was very clear before in my original post, and I apologize for that.  The Monday date would be the computer's system date, not a variable field in the input data set.  Should the syntax you provided process the computer's system date?  

Thank you again.

Errol Isenberg

[vizAlter]

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

[Qiu]

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

[Errol]

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"));