Suzie's PySpark and Incorta Notes

 This part is about the holiday. I have two columns, holiday name, and day type. Use Spark SQL to complete. Below is a screenshot of the dashboard in Incorta. For more details, please see my previous blog. https://suziepyspark.blogspot.com/2020/10/using-pyspark-to-get-holidays-in-incorta.html

 The third part is the PySpark dataframe, this part is the most challenging part for me. Writing this part with PySpark is simpler and easier since we can get the data we want based on the previous result. For example, fiscal quarter seq and fiscal week of quarter can be built on the top of fiscal quarter number, and so on. I also practice how to use the window function in dataframe. For example, calculating fiscal year end date, fiscal month start date, fiscal month end date, and so on.  For getting the fiscal quarter number, we can use divide the week of year by 13.  For getting the fiscal month of quarter, we can depend on the fiscal week of quarter. When fiscal week of quarter <= 4, it is the first month of the quarter, and the month of quarter is 1. When fiscal week of quarter <=4+5, fiscal week of quarter is 2.  When fiscal week of quarter <=4+5+4, fiscal week of quarter is 3. Otherwise, when there are more than 52 weeks in a year, fiscal week of ...

This is the second part of the fiscal calendar MV, the Spark SQL part, through this part we can get day number, day name, fiscal week number, fiscal year start date, fiscal week end date, fiscal week of year, fiscal week start date, fiscal day seq, fiscal week date, fiscal day seq, fiscal week seq, fiscal year seq, fiscal day ago date, fiscal week ago date.  Divide the day of the year by 7, and if the remainder is 0, the week number is 7, which represents Sunday. Other week numbers are remainders.  Pushing the current date backward the days of the year is the fiscal year's start date.  To get the fiscal week of year. floor((doy-1)/7) . divided day of the year by 7 and remove the decimal. To get the fiscal week end date, first I get the fiscal week of the year floor((doy-1)/7) , and get the current number of days from the last day of the week through (((floor((doy-1)/7))+1)*7)-doy , then returns the week end date that is the num days after the date date_add(date, num_...

I will refer to the fiscal calendar from the National Retail Federation and create the same fiscal calendar in Incorta. https://nrf.com/resources/4-5-4-calendar First, I created a materialized view using PySpark in Incorta. You can click this link to see how to use PySpark to create a materialized view in Incorta.  https://community.incorta.com/t/x1jhfc/creating-materialized-view-using-pyspark In the python part, I generated the fiscal year according to the number of days within a year.  In a week based fiscal calendar, a year can have either 52 or 53 weeks. I use the number of weeks and the days of year to determine the start of years. This is the first part of the fiscal calendar. It is now a dataframe with three columns. the 'idx' column with the sequence number, 'doy' column with the day of the years, and 'fiscal year' column.  In the second part, I will explain how to get the data we need such as fiscal_week_number, fiscal_year_start_date, fiscal_week_of_y...