In Monarch Classic or Data Prep, how to capture last 7 days of data

Cindy Razzi_20472
Cindy Razzi_20472 Altair Community Member
edited May 31 in Community Q&A

I have a text file and created a model to capture the data but only want to pull in the last 7 days based on transaction date. I have tried various things (filters, calculated formulas, etc) in Monarch Classic and Data Prep but unable to capture all of the dates. For example, today is 05/30/24 and want data from 05/22/24 thru 05/29/24.  Thanks

Tagged:

Answers

  • Shelton Vance_22282
    Shelton Vance_22282 Altair Community Member
    edited May 30

    Not an expert at this, but here is what I would do.

     

    I would create a Formula Field that sets the value to "1" or "Y" if the record's date field is greater than / equal to DATE() - 7.  I would probably hide this field.

    I would then filter for that value.

    Each calendar day that you open the file, the value of that field would change, resulting in a revised data set, using the same report.   This is a drawback to this solution.

    You might also consider picking up a "report date" by the append or page header function.  You could then create your formula field using "report date" - 7.  With this, you could return to the report and obtain the same data from the same report at a future time.

  • CPorthouse
    CPorthouse
    Altair Employee
    edited May 31

    If it is a text file, you will have to completely load it and then use various combinations of formula columns and filters to display just the data you want.  If you had a database source, you could potentially use a query to limit what is loaded.  Based on your question, here is what I would propose for a text file.

    Once the file is loaded, create a formula column using the Age() function.  There is a couple of ways to approach this.  You can either calculate the number of days between today's date and your transaction date and return that number:

    Age([Transaction Date],Date(),4)

    or use that value to return a true or false:

    if(age([Transaction Date],date(),4)<=7, "True","False")

    You can then create an appropriate filter based on the column you created.  You can always hide (or delete if using DPS) the formula column used for the filter if you do not want to include it in an export.

    HTH.

Categories