Partition Variable for previous day, previous week....

Options
dmajard
dmajard Registered Posts: 6 ✭✭✭✭

Hi all,

The partition variables such as CURRENT_DAY are apparently available but it it impossible to find how to get 1_DAYS_BEFORE to work in SQL Recipes for manual partition specification.

The log shows that they are available though:


"DKU_DST_YEAR": "2019",
"DKU_DST_MONTH_7DAYSBEFORE": "03",
"DKU_DST_DATE_1DAYBEFORE": "2019-03-18",
"DKU_DST_DATE": "2019-03-19",
"tbl:hourly_summary": "\"dataiku\".\"ZIZZI_UK_hourly_summary\"",
"DKU_DST_MONTH_1DAYAFTER": "03",
"schema:hourly_summary": "\"dataiku\"",
"DKU_DST_partition_first_day_of_week": "2019-03-19",
"DKU_DST_MONTH_1DAYBEFORE": "03",
"DKU_DST_DAY_1DAYBEFORE": "18",
"DKU_DST_DAY_7DAYSBEFORE": "12",
"DKU_DST_DATE_7DAYSBEFORE": "2019-03-12",
"schema:transactions_table_restaurants": "\"dataiku\"",
"DKU_DST_DAY": "19",
"DKU_DST_DATE_1DAYAFTER": "2019-03-20",
"DKU_DST_DAY_1DAYAFTER": "20",
"DKU_DST_MONTH": "03",
"DKU_DST_YEAR_1DAYAFTER": "2019",
"DKU_DST_YEAR_1DAYBEFORE": "2019",
"DKU_DST_YEAR_7DAYSBEFORE": "2019",

Any guidance on that?

Cheers,

Dany

Answers

  • AdrienL
    AdrienL Dataiker, Alpha Tester Posts: 196 Dataiker
    Options
    The equivalent for CURRENT_DAY but the day before is PREVIOUS_DAY. You have CURRENT_ and PREVIOUS_ for YEAR, MONTH, DAY and HOUR.
  • dmajard
    dmajard Registered Posts: 6 ✭✭✭✭
    Options
    Thank you Adrien.

    This was the stage you were at two years ago, I was hoping you had enacted those of the type 7DAYSBEFORE. It is quite tricky to only have current and previous for data that does not come on a daily or monthly basis (weekly for example).

    In our case we wish to populate two partitions which are the current day and that of last week.

    Hoping you can help.
  • AdrienL
    AdrienL Dataiker, Alpha Tester Posts: 196 Dataiker
    Options
    It is not directly addressable, but can be computed as part of a scenario, put in a scenario variable and that variable used for building your dataset.
  • dmajard
    dmajard Registered Posts: 6 ✭✭✭✭
    Options
    I tested this approach, but I am afraid there are issues.
    I defined the following variable in a Define scenario variable steps, with the DSS evaluation on:
    inc(now(),-${LOOKBACK_WEEKS},'week')
    where the LOOKBACK_WEEKS is a global variable of the scenario.
    Though the formula works in a prepare node, the scenario step cannot retrieve the variable.
    According to this page (https://doc.dataiku.com/dss/latest/scenarios/variables.html) it should.
    What am I missing?
  • AdrienL
    AdrienL Dataiker, Alpha Tester Posts: 196 Dataiker
    Options
    Please download a scenario diagnostic (from the scenario page, under the "Last runs" tab, "download diagnostic" link on top of the page) and open a support ticket (from the "?" top-right menu > Get Help), including your diagnostic and a link this page.
Setup Info
    Tags
      Help me…