Icon

Rossmann - DB - Time Series(2)

Data Preparation:Aggregate daily Rossmann sales data into monthly data Advanced Data Preparation -Automation of appendingdata 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 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 Working AreaTestoweoperacje train1,017,209 rows9 cols;limit 10kchange 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 loopexecute hereif wanna proceedthis loopROSSMANN append weatherlocCharreplace escape charsget StateNamefrom pathget StateNamefrom pathget StateNamefrom pathmake flow var as df variablerm 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 dfstore id as string1.data audit(źle czyta typy na limicie stąd wykrzyknik)backup for performance issuesweather data audit6. R:Scatterplot of Sales in 1:10 Storesreplace 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)min 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 (?)KNIME nie czyta tibbleback 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.csvmany columnsto be renamed/removedas containing invalid characters ...user passlogin1. trainmonthly33,485 rows3 last months as holdout3 last months as holdoutNode 5132. Selecting, filtering, aggreg- Baza danych- KNIME TablePrzejście z bazy do tabeli KNIMENode 519Node 520rm first 12M& last 2M CSV Reader String to Date&Time Extract Date&TimeFields Math Formula Flow variables(Parameters) Math Formula Rule-basedRow Filter PostgreSQLConnector DB Table Selector ETL Workshop Data 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 Number To String(deprecated) Statistics Sales Chart - One Store(from Flow Variable) CSV Writer Statistics 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 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 DB Writer PostgreSQLConnector Timer Info CSV Reader Column Filter Column Rename H20 Gradient BoostingMachine Holdout R Forecast Holdout DB Connector Excel Reader (XLS) Basic KNIMEoperations DB Reader Color Manager Linear Correlation Rule-basedRow Splitter Data Preparation:Aggregate daily Rossmann sales data into monthly data Advanced Data Preparation -Automation of appendingdata 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 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 Working AreaTestoweoperacje train1,017,209 rows9 cols;limit 10kchange 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 loopexecute hereif wanna proceedthis loopROSSMANN append weatherlocCharreplace escape charsget StateNamefrom pathget StateNamefrom pathget StateNamefrom pathmake flow var as df variablerm 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 dfstore id as string1.data audit(źle czyta typy na limicie stąd wykrzyknik)backup for performance issuesweather data audit6. R:Scatterplot of Sales in 1:10 Storesreplace 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)min 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 (?)KNIME nie czyta tibbleback 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.csvmany columnsto be renamed/removedas containing invalid characters ...user passlogin1. trainmonthly33,485 rows3 last months as holdout3 last months as holdoutNode 5132. Selecting, filtering, aggreg- Baza danych- KNIME TablePrzejście z bazy do tabeli KNIMENode 519Node 520rm first 12M& last 2M CSV Reader String to Date&Time Extract Date&TimeFields Math Formula Flow variables(Parameters) Math Formula Rule-basedRow Filter PostgreSQLConnector DB Table Selector ETL Workshop Data 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 Number To String(deprecated) Statistics Sales Chart - One Store(from Flow Variable) CSV Writer Statistics 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 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 DB Writer PostgreSQLConnector Timer Info CSV Reader Column Filter Column Rename H20 Gradient BoostingMachine Holdout R Forecast Holdout DB Connector Excel Reader (XLS) Basic KNIMEoperations DB Reader Color Manager Linear Correlation Rule-basedRow Splitter

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-26
Last update: 2024-04-25
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!