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

dmajard
Level 2
Partition Variable for previous day, previous week....

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

0 Kudos
5 Replies
AdrienL
Dataiker
The equivalent for CURRENT_DAY but the day before is PREVIOUS_DAY. You have CURRENT_ and PREVIOUS_ for YEAR, MONTH, DAY and HOUR.
0 Kudos
dmajard
Level 2
Author
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.
0 Kudos
AdrienL
Dataiker
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.
0 Kudos
dmajard
Level 2
Author
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?
0 Kudos
AdrienL
Dataiker
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.
0 Kudos

Labels

?
Labels (4)
A banner prompting to get Dataiku