Forum Discussion

CharlesDulaney's avatar
CharlesDulaney
Qrew Member
8 months ago

Create a Table of all Sundays within a Year

I have a parent table [year] and its child table is [week_ending_dates].

[year] ------------> [week_ending_dates]

A pipeline runs annually on 1 Jan of every year and creates the New Year's date of 1 JAN YYYY in the parent table record date field.

The parent record creation triggers another pipeline to populate the related child table [week_ending_dates] with every Sunday's date for that year. One date, one record. 

Well, that's where it ends for me....

I am seeking some insight into creating the pipeline loop that creates each Sunday's date, as a record for the parent year. Seeking full automation and trying to avoid any table imports or manual work.



------------------------------
v/r,
Chuck
------------------------------

7 Replies

  • Are you opposed to just doing a one time import? You can drag down the next 10 years of Sundays in Excel and just pre-load them now instead of building the Pipeline / testing it / monitoring it. 

    You can just pre-load 5, 10, 15 years worth of data and if you don't want to see any of it just add a simple 'valid' checkbox to hide any futures you don't want and hide them via role security so that you don't have to worry about them being there they just re-appear when relevant. 



    ------------------------------
    Chayce Duncan
    ------------------------------
    • PrashantMaheshw's avatar
      PrashantMaheshw
      Qrew Captain

      "You can just pre-load 5, 10, 15 years worth of data and if you don't want to see any of it just add a simple 'valid' checkbox to hide any futures you don't want and hide them via role security so that you don't have to worry about them being there they just re-appear when relevant."

      Words of wisdom by @Chayce Duncan . I too have preloaded calendars for next 50 years , Clearn and simple



      ------------------------------
      Prashant Maheshwari
      ------------------------------
      • CharlesDulaney's avatar
        CharlesDulaney
        Qrew Member

        agreed,



        ------------------------------
        v/r,
        Chuck
        ------------------------------
    • CharlesDulaney's avatar
      CharlesDulaney
      Qrew Member

      some things are not worth the pain.

      I opted this path - too easy.

      Thank you,



      ------------------------------
      v/r,
      Chuck
      ------------------------------
  • DougHenning1's avatar
    DougHenning1
    Community Manager

    I realize you already have an Excel solution, but if you want to use pipelines, this jinja will generate all the Sunday dates in a specified year separated by a semicolon:

    {% set startDate = time.parse("1/1/2024") -%}
    {% set endDate = startDate + time.delta(month=12,day=31) -%}
    {% set numDays = (endDate - startDate).days -%}
    {% for x in range(0, numDays + 1) -%}
    {% set dateRec = startDate + time.delta(days=x) -%}
    {% if (dateRec.weekday() == 6) -%}
    {{ dateRec|date_ymd }}{{ ';' if not loop.last }}
    {%- endif -%}
    {% endfor -%}

    Use in a Regex search step and a loop of the results.



    ------------------------------
    Doug Henning
    ------------------------------