Forum Discussion

CharlesDulaney's avatar
CharlesDulaney
Qrew Trainee
2 years 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
------------------------------
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    2 years ago

    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
      2 years ago

      "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 Trainee
        2 years ago

        agreed,



        ------------------------------
        v/r,
        Chuck
        ------------------------------
    • CharlesDulaney's avatar
      CharlesDulaney
      Qrew Trainee
      2 years ago

      some things are not worth the pain.

      I opted this path - too easy.

      Thank you,



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

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