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

dmajard

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

AdrienL

The equivalent for CURRENT_DAY but the day before is PREVIOUS_DAY. You have CURRENT_ and PREVIOUS_ for YEAR, MONTH, DAY and HOUR.

dmajard

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

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

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

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.