Icon

Rossmann - DB - Time Series

Data Preparation:Aggregate daily Rossmann sales data into monthly data Advanced Data Preparation -Automation of appending data from disparate filesandJoining weather data with sales data Data Preparation:Calculating new variables based on existing column in sales data Data Preparation:Filtering data and selecting 4 store for example analysis Data Preparation:Calculating new variables based on lagged values of weather (+1,+2) and sales and customer data (-12) Data Preparation:Appending together spllited store data and filtering unused in modellingrows Prediction 2015, last 3 Months Database Data Preparation:Aggregate daily Rossmann sales data into monthly data Working AreaTestoweoperacje Database Data Preparation:Calculating new variables based on existing column in sales data Database Data Preparation:Filtering data and selecting & all stores included R Data Preparation:Calculating new variables based on lagged values of weather (+1,+2) and sales and customer data (-12) Data Preparation:Appending together spllited store data and filtering unused in modellingrows Prediction 2015, last 3 Months 1. train1,017,209 rows9 colschange date from default string storage get Year, Month etc from datevar:SalesPerCustomercreate flow variablesvar:Cost (as 0.8*Sales+ random() )Select 10 storesuser passlogin1. train1,017,209 rows9 colsexclude Open = 0and Sales = 0AggregateStores to Monthchange StateHolto integerreplace inString columnStateHolidaycreate Date (Monthly)change date from default string storage 3. KNIME TotalSalesin 10 stores5. KNIME Sales in time for 10 Stores4. R:Promo and Salesper StoreTypeweather csv'sfolder- must be executedbefore loopstart loop-must be executedbefore loopimport i'thcsv fileend loopROSSMANN append weatherget StateNamefrom pathget StateNamefrom pathget StateNamefrom pathget StateNamefrom pathget StateNamefrom pathget StateNamefrom pathrm tempvariablesget StateCodestore_statestate to statecodeget Stateto traintrainROSSMANN append weather.csvget Stateto trainNode 333train_added_weather.csvvar:Promo2Openvar:StateHoliday2Openvar:SchoolHoliday2OpenSelect 4 stores:9, 27, 34, 262sort asc by dateNode 344max tempmean tempsort asc by datefrom x(t) to: x(t), x(t-1), x(t-2), ..., x(t-lag)from x(t) to: x(t), x(t-1), x(t-2), ..., x(t-lag)append allstores toone dfrm first 12M& last 2Mstore id as string2.data auditbackup if performance issuesweather data audit3 last months as holdoutNode 434replace inString columnStateHolidayworkaround::unfilterworkaround::rename to originalget Year, Month etc from datecreate Date (Monthly)AggregateStores to Monthchange date from default string storage unfilterrename to originalmultiple vars:(as below)Node 455Node 456min tempexclude Open = 0and Sales = 0Select 4 stores:9, 27, 34, 262sort asc by dateOut of DBto KNIMEobject [table]lags[1:n]base solutionback toKNIME tablelags[1:n]advanced solutionnot working here (?)back toKNIME tablesome other lagsstore id as stringsome other lagschange date from default string storage Select 4 stores:9, 27, 34, 262R Forecastauto.arima()Select 1 storestore.csvcreate flow variablesnie da się inputowaćlisty do tego węzła wybieranie z listy 3 last months as holdoutNode 500user passlogin CSV Reader String to Date&Time Extract Date&TimeFields Math Formula Flow variables(Parameters) Math Formula Rule-basedRow Filter PostgreSQLConnector DB Table Selector Rule-basedRow Filter GroupBy String To Number(deprecated) Rule Engine String Manipulation String to Date&Time Histogram Sales, Cust, Salesper Cust - Charts Store Type and PromotionInfluence Charts List Files Table Row ToVariable Loop Start File Reader Loop End CSV Writer String Manipulation String Replacer String Manipulation String Manipulation String Manipulation String Manipulation Column Filter Joiner File Reader File Reader Joiner CSV Reader CSV Reader Joiner Row Filter CSV Writer Math Formula Math Formula Math Formula Rule-basedRow Filter Sorter Row Filter Row Filter Row Filter Row Filter Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Sorter Lag Column Lag Column Sorter Sorter Sorter Concatenate Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Concatenate Concatenate Rule-basedRow Splitter Number To String(deprecated) Statistics Sales Chart - One Store(from Flow Variable) Timer Info CSV Writer Statistics H20 Gradient BoostingMachine Holdout Metanode Load/WriteRossmann Table DB Query DB Column Filter DB Column Rename DB Query DB Query DB GroupBy DB Type Mapper DB Column Filter DB Column Rename DB Query Moving Average Moving Aggregation Lag Column DB Row Filter DB Row Filter DB Sorter DB Reader Table to R R to Table Table to R R to Table R To R Number To String R To R String to Date&Time Rule-basedRow Filter Table to R Rule-basedRow Filter CSV Reader Flow variables(Parameters) Row Filter Nominal ValueRow Filter R Forecast Holdout DB Writer PostgreSQLConnector Data Preparation:Aggregate daily Rossmann sales data into monthly data Advanced Data Preparation -Automation of appending data from disparate filesandJoining weather data with sales data Data Preparation:Calculating new variables based on existing column in sales data Data Preparation:Filtering data and selecting 4 store for example analysis Data Preparation:Calculating new variables based on lagged values of weather (+1,+2) and sales and customer data (-12) Data Preparation:Appending together spllited store data and filtering unused in modellingrows Prediction 2015, last 3 Months Database Data Preparation:Aggregate daily Rossmann sales data into monthly data Working AreaTestoweoperacje Database Data Preparation:Calculating new variables based on existing column in sales data Database Data Preparation:Filtering data and selecting & all stores included R Data Preparation:Calculating new variables based on lagged values of weather (+1,+2) and sales and customer data (-12) Data Preparation:Appending together spllited store data and filtering unused in modellingrows Prediction 2015, last 3 Months 1. train1,017,209 rows9 colschange date from default string storage get Year, Month etc from datevar:SalesPerCustomercreate flow variablesvar:Cost (as 0.8*Sales+ random() )Select 10 storesuser passlogin1. train1,017,209 rows9 colsexclude Open = 0and Sales = 0AggregateStores to Monthchange StateHolto integerreplace inString columnStateHolidaycreate Date (Monthly)change date from default string storage 3. KNIME TotalSalesin 10 stores5. KNIME Sales in time for 10 Stores4. R:Promo and Salesper StoreTypeweather csv'sfolder- must be executedbefore loopstart loop-must be executedbefore loopimport i'thcsv fileend loopROSSMANN append weatherget StateNamefrom pathget StateNamefrom pathget StateNamefrom pathget StateNamefrom pathget StateNamefrom pathget StateNamefrom pathrm tempvariablesget StateCodestore_statestate to statecodeget Stateto traintrainROSSMANN append weather.csvget Stateto trainNode 333train_added_weather.csvvar:Promo2Openvar:StateHoliday2Openvar:SchoolHoliday2OpenSelect 4 stores:9, 27, 34, 262sort asc by dateNode 344max tempmean tempsort asc by datefrom x(t) to: x(t), x(t-1), x(t-2), ..., x(t-lag)from x(t) to: x(t), x(t-1), x(t-2), ..., x(t-lag)append allstores toone dfrm first 12M& last 2Mstore id as string2.data auditbackup if performance issuesweather data audit3 last months as holdoutNode 434replace inString columnStateHolidayworkaround::unfilterworkaround::rename to originalget Year, Month etc from datecreate Date (Monthly)AggregateStores to Monthchange date from default string storage unfilterrename to originalmultiple vars:(as below)Node 455Node 456min tempexclude Open = 0and Sales = 0Select 4 stores:9, 27, 34, 262sort asc by dateOut of DBto KNIMEobject [table]lags[1:n]base solutionback toKNIME tablelags[1:n]advanced solutionnot working here (?)back toKNIME tablesome other lagsstore id as stringsome other lagschange date from default string storage Select 4 stores:9, 27, 34, 262R Forecastauto.arima()Select 1 storestore.csvcreate flow variablesnie da się inputowaćlisty do tego węzła wybieranie z listy 3 last months as holdoutNode 500user passlogin CSV Reader String to Date&Time Extract Date&TimeFields Math Formula Flow variables(Parameters) Math Formula Rule-basedRow Filter PostgreSQLConnector DB Table Selector Rule-basedRow Filter GroupBy String To Number(deprecated) Rule Engine String Manipulation String to Date&Time Histogram Sales, Cust, Salesper Cust - Charts Store Type and PromotionInfluence Charts List Files Table Row ToVariable Loop Start File Reader Loop End CSV Writer String Manipulation String Replacer String Manipulation String Manipulation String Manipulation String Manipulation Column Filter Joiner File Reader File Reader Joiner CSV Reader CSV Reader Joiner Row Filter CSV Writer Math Formula Math Formula Math Formula Rule-basedRow Filter Sorter Row Filter Row Filter Row Filter Row Filter Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Sorter Lag Column Lag Column Sorter Sorter Sorter Concatenate Lag Column Lag Column Lag Column Lag Column Lag Column Lag Column Concatenate Concatenate Rule-basedRow Splitter Number To String(deprecated) Statistics Sales Chart - One Store(from Flow Variable) Timer Info CSV Writer Statistics H20 Gradient BoostingMachine Holdout Metanode Load/WriteRossmann Table DB Query DB Column Filter DB Column Rename DB Query DB Query DB GroupBy DB Type Mapper DB Column Filter DB Column Rename DB Query Moving Average Moving Aggregation Lag Column DB Row Filter DB Row Filter DB Sorter DB Reader Table to R R to Table Table to R R to Table R To R Number To String R To R String to Date&Time Rule-basedRow Filter Table to R Rule-basedRow Filter CSV Reader Flow variables(Parameters) Row Filter Nominal ValueRow Filter R Forecast Holdout DB Writer PostgreSQLConnector

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: mwysocki
Created at: 2018-04-19
On NodePit since: 2019-10-18
Last update: 2024-04-26
Created with KNIME version: v4.0.2

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!