Icon

Solution

PART 1: We want to read in the data from a number of Excelsheets that are located within the same workbook - AirBnBListings.XLSX.Find a node that allows you to generate a list of the sheet namesfrom a given Excel file. How many sheets are in the AirBnBListings workbook? PART 2: Next, we want to take the information from each sheetand combine them into a single data table in KNIME.Determine which type of loop is most appropriate for thispurpose. Place the start node of the loop in this box. PART 3: With the loop initiated, what goes inside the loop? Recall, we want to read datafrom an Excel file where the sheet name changes with each loop iteration.- Dynamically update the sheet name with each iteration.- You must devise a strategy to include the sheet name as a column. Name this column"Location" and ensure it is the first column in the table. PART 4: End the loop, do not create an iteration column,generate new row IDs.Determine which location charges the highest average price. Troubleshooting:- If you run into an error reading in the data using the Excel Reader node, navigate to theTransformations tab in the configuration menu and change the field type of id and host_idto strings.Hints (move the black box to reveal):- In the Excel Reader, you must enable "Select sheet with name" for the flow variable todynamically change the sheet names. - On the Flow Variables tab, you need to update the sheet_name variable.- You need to convert a variable to a table row value. This is done via the Variable to TableRow node.- To combine this single value with every row of the Excel data, use a Cross Joiner. AirBnB ListingssheetsStart the loopRead in data from eachExcel sheetConvert the sheetvariable to a table rowAttach the sheetname to each row in the tablePlace the Sheetfield firstRename Sheetto LocationEnd the loopSummarize, meanprice by locationMean price indescending order Read ExcelSheet Names Table Row ToVariable Loop Start Excel Reader Variable toTable Row Cross Joiner Column Resorter Column Rename Loop End GroupBy Sorter PART 1: We want to read in the data from a number of Excelsheets that are located within the same workbook - AirBnBListings.XLSX.Find a node that allows you to generate a list of the sheet namesfrom a given Excel file. How many sheets are in the AirBnBListings workbook? PART 2: Next, we want to take the information from each sheetand combine them into a single data table in KNIME.Determine which type of loop is most appropriate for thispurpose. Place the start node of the loop in this box. PART 3: With the loop initiated, what goes inside the loop? Recall, we want to read datafrom an Excel file where the sheet name changes with each loop iteration.- Dynamically update the sheet name with each iteration.- You must devise a strategy to include the sheet name as a column. Name this column"Location" and ensure it is the first column in the table. PART 4: End the loop, do not create an iteration column,generate new row IDs.Determine which location charges the highest average price. Troubleshooting:- If you run into an error reading in the data using the Excel Reader node, navigate to theTransformations tab in the configuration menu and change the field type of id and host_idto strings.Hints (move the black box to reveal):- In the Excel Reader, you must enable "Select sheet with name" for the flow variable todynamically change the sheet names. - On the Flow Variables tab, you need to update the sheet_name variable.- You need to convert a variable to a table row value. This is done via the Variable to TableRow node.- To combine this single value with every row of the Excel data, use a Cross Joiner. AirBnB ListingssheetsStart the loopRead in data from eachExcel sheetConvert the sheetvariable to a table rowAttach the sheetname to each row in the tablePlace the Sheetfield firstRename Sheetto LocationEnd the loopSummarize, meanprice by locationMean price indescending order Read ExcelSheet Names Table Row ToVariable Loop Start Excel Reader Variable toTable Row Cross Joiner Column Resorter Column Rename Loop End GroupBy Sorter

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: John Emery
Created at: 2023-03-28
On NodePit since: 2023-08-16
Last update: 2024-04-27
Created with KNIME version: v4.7.0

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

Try NodePit Runner!